Skip to main content

Formula Wizard

The Formula Wizard is a user-friendly interface that helps you generate a NECFORMULA function in a cell. NECFORMULA is a custom function that extracts data from a data model based on the criteria set in the Formula Wizard. It then aggregates the values to answer a specific question.

Using the Formula Wizard, you can select the environment, data model, field, and operator for your formula. You can also add filters, create filter groups, and preview the formula result.

You can access the Formula Wizard from the Analysis tab of a workbook.

When to use the Formula Wizard​

  • Simplifying calculations: Helps build complex formulas without needing advanced technical skills.
  • Speeding up analysis: Allow quick data manipulation and analysis using predefined data models.
  • Reducing errors: Minimizes errors by providing syntax assistance and error-checking features.
  • Saving time: Avoid the need to learn complex formula syntax, letting you focus on analysis.
  • Ensuring integration: Work with NECFORMULA to retrieve data from data models while maintaining user-level data security.

Create a formula with the Formula Wizard​

You can create a formula to retrieve the specific data you need.

  1. Select the cell where you want to insert the formula.
  2. On the Analysis tab, select Formula Wizard.
  3. From the Environment and Data Model dropdown menus, select the environment and data model.
  4. In the Field dropdown menu, choose the field for the formula.
  5. Select an operator from the Operator dropdown menu.
  6. To narrow your results, select Add Filter or Add Group. Adding filters help target specific subsets of data, so you only create the pivot table you need.
  7. Use And or Or to create logical relationships between filters or groups.
  8. Select Preview Value to confirm the results before saving.
  9. Select Save to apply the formula.

For a detailed example, see [Formula Wizard Example].

Name Multiple Formulas in a Cell​

When a cell contains multiple NECFORMULA functions, the Formula Wizard lets you assign a custom name to each one:

  1. Select a cell that contains multiple NECFORMULA functions.
  2. On the Analysis tab, select Formula Wizard.
  3. In the list on the left, select the pencil icon next to the formula you want to rename.
  4. Click Save. The custom names are applied to the NECFORMULA functions in the cell.

Refresh a formula wizard​

Update a formula wizard with the latest data from your data source to ensure your analysis reflects the most current information.

  1. On the Toolbar panel, select Refresh.
  2. Ensure the Formulas slider is toggled.
  3. Select Entire Workbook to refresh all formula wizards, or Current Worksheet to refresh only those in the current worksheet.

Drill down on a NECFORMULA​

Drill down on a NECFORMULA in the workbook to gain more detailed insight into the data behind the formula.

From the ribbon​

  1. Select a cell that contains a NECFORMULA.
  2. On the Analysis tab, in the Tools section, select Drill Down.
  3. The Drill Down dialog opens and shows the formula details.

From the toolbar​

  1. Select a cell that contains a NECFORMULA.
  2. On the Toolbar tab on the right, select Drill Down.
  3. The Drill Down dialog opens and shows the formula details.

For more information about setting up a drill down profile, see Create a drill down.

Formula Wizard properties​

Fields​

PropertyDescription
EnvironmentDefines the environment for duplication. If set to Current Environment, the data is retrieved dynamically from the active environment.
Data ModelDefines the data model for the formula.
FieldsSpecifies the field for the formula. This can be a dimension, measure, or description.
OperatorSpecifies the operator for the formula. The available operators depend on the data model and field, may include:

  • Avg
  • Count
  • Count Distinct
  • Description
  • Dynamic List
  • Min
  • Max
  • Sum
  • Sum (Inversion)
  • Sum (Credit)
  • Cum (Debit)

Filters​

PropertyDescription
And, OrDefines the logical relationship between filters or groups using And or Or.
Add Filter, Add Group
  • Add Filter – Adds an individual filter.
  • Add Group – Adds a set of filters that are grouped together.
For each filter, specify the Field, Operator, and Value. You can enter manually or reference in a cell. Use the prompt icon to select a field.

To reference a cell, click the underscore in the Value field, then select the cell. This applies to both individual filters and groups.

By default, character values are treated as cell references. To enter a text value instead, click the A icon next to the value field. When active, the icon turns blue, indicating the input is treated as a text. If you use the prompt, it's automatically set as text.
Clear all filtersRemoves all filters and groups.

Options​

PropertyDescription
Preview ValueDisplays the formula result. If the formula is invalid, an error message appears. A timeout occurs if query time exceeds 300 seconds (5 minutes) or if the formula reaches 500,000 rows.
SaveSaves the formula. The workbook generates a function that is visible in the formula bar. Clicking the formula bar opens a dialog showing the NECFORMULA function (similar to NECACCESS in Excel Add-in).

Unlike Excel Add-in, no reference configuration is required. Smart caching applies automatically based on the formula.
XCloses the wizard without saving.