Skip to main content

Formula Wizard

The Formula Wizard is a user-friendly tool 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​

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.
  10. Click Save.

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 panel 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
EnvironmentDefine the environment for duplication. If set to Current Environment, data is retrieved dynamically from the active environment.
Data ModelDefine the data model for the formula.
FieldsSpecify the field for the formula. This can be a dimension, measure, or description.
OperatorSpecify the operator for the formula. Available operators depend on the data model and field, and may include:

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

Filters​

PropertyDescription
And, OrDefine the logical relationship between filters or groups using And or Or.
Add Filter, Add Group
  • Add Filter – Add an individual filter.
  • Add Group – Add a set of filters grouped together.
For each filter, specify the Field, Operator, and Value. Enter values manually or reference a cell. Use the prompt icon to select a field.

To reference a cell, select 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, select the A icon next to the value field. When active, the icon turns blue, indicating the input is treated as text. If you use the prompt, it is automatically set as text.

Use the prompt icon to select a field or value. You can also use wildcards to filter for specific values.
Clear all filtersRemove all filters and groups.

Use of wildcards for filtering​

You can use wildcards to filter only to text values, not numbers. Wildcards help you find values that match specific patterns, such as those that contain, start with, or end with certain characters.

  • ? replaces one character
  • * replaces multiple characters
Wildcard exempleDescription
NAMust match NA.
NA*Must start with NA.
NA???Must start with NA and contain exactly four characters.
NA???-???-AMust start with NA, followed by any three characters, a -, three more characters, another -, and end with A (total length: 11 characters).

Options​

OptionDescription
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 returns more than 500,000 rows.
SaveSaves the formula. The workbook generates a function visible in the formula bar. Selecting the formula bar opens a dialog showing the NECFORMULA function (similar to NECACCESS in the Excel Add-in).

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