Skip to main content

Formula Wizard

The Formula Wizard is a user-friendly interface that assists users in generating a NECFORMULA function within a cell. The NECFORMULA is a custom function that extracts data from a data model based off the information set within the Formula Wizard. This is then used to aggregate the values to answer a specific question. Using the Formula Wizard, you can select the environment, data model, field, and operator you want for your formula. You can also add filters, add groups of filters, and preview a formula result. It is located under the Analysis tab of the workbook.

Benefits​

Why use the Formula Wizard?

  • Ease of use: Simplifies the process of creating complex calculations, making it accessible to users with varying levels of technical expertise.
  • Efficiency: Speeds up the process of data manipulation and analysis based off the data dodels, reducing the need for manual calculations.
  • Accuracy: Reduces errors by providing syntax assistance and error-checking features.
  • Time saving: Reduces the time required to learn complex formula syntax, allowing users to focus on analysis rather than formula creation.
  • Integration: Seamlessly integrates with the custom formula NECFORMULA, allowing for data retrieval from existing data models while ensuring granular user data security.

Create a Formula with the Formula Wizard​

Name Multiple Formulas in a Cell​

When using more than one NECFORMULA in a cell, the Formula Wizard gives you the option to name each formula:

  1. Select a cell with multiple NECFORMULAs, then click Formula Wizard from the Analysis tab.

  2. In the list on the left, click the to rename the NECFORMULA of your choice.

  3. Once you save the changes, the custom names are added to the NECFORMULA in the cell.

Formula Wizard Properties​

The Formula Wizard includes sections to define the environment, data model, field, and operator for your formula, as well as a section where you can opt to add a filter or a group.

PropertyDescription
EnvironmentSet the environment for your formula. All available environments in the Web Server are listed. When set as Current Env., it will update the formula to retrieve the data of the environment selected.
Data ModelSelect the data model you are applying the formula to. You can select it from the drop-down or type it in for a quicker selection.
FieldSelect the field you are applying the formula to. Depending on the field, this can be a dimension, measure, or description. You can select it from the drop-down or type it in for a quicker selection.
Operator

Choose the operator for the formula. You can select it from the drop-down or type it in for a quicker selection. The available operators change depending on the data model and field, but generally you can select from the following:

  • Avg

  • Count

  • Count Distinct

  • Description

  • Dynamic List

  • Min

  • Max

  • Sum

  • Sum (Inversion)

  • Sum (Credit)

  • Sum (Debit)

Add Filter / Add Group

Select Add Filter to add filters to the formula or select Add Group to add a set of filters that are grouped together. For both, you must also select the Field, Operator, and Value. The Value can be a referenced cell. You can use the prompt to select the field.

Reference a cell

If you wish to reference a cell, click the underscore in the Value field and select the cell. You can also enter the value in the cell. This is also applicable for Groups.

Cell reference character values

When you enter a character value, the workbook assumes that it is a cell reference by default. To specify the value as text, click the icon; its color then changes to blue .

When selecting a character value using the prompt, the value is set to text automatically.

For formula examples, refer to Workbook Formula Examples.

And / OrSet Filters or Groups to AND or OR by clicking on the corresponding button.
Clear all filtersClear all filters and groups in the Formula Wizard.
CancelClose the Formula Wizard window (formulas are not saved automatically).
Preview Value

Preview the formula result. If the formula is incorrect, a message will be displayed.

When resolving the formula, a timeout happens when the querying reaches 300 seconds (5 minutes) or if the data results retrieved for the formula reaches 500,000.

Save

Save the formula(s). The workbook generates a function that is visible in the formula bar. When clicking the formula bar, it displays a pop-up that shows the NECFORMULA function (similar to the NECACCESS function in the Excel Add-in).

As opposed to the Excel Add-in, you do not need to create a reference configuration. This is done automatically according to the created formula thanks to smart caching.