Entering Formulas

You can type formulas manually, or use the formula editor if you're not sure how to construct the formula you want.

Context

These are the formula syntaxes you can use when entering formulas in the Modeler, or when using the calculation editor in stories, or when creating formulas in data actions.

Note
In stories, the identifiers are qualified with the model ID. For example:

An account member: ["BusinessPlanning_BASE":OPERATING PROFIT]

A dimension: [d/"BusinessPlanning_BASE":Product]

Syntax Description Example
[IDENTIFIER] An account member or measure can be used in a formula by enclosing the ID inside brackets: [ ] [PROFIT]
[d/DIMENSION_IDENTIFIER] A dimension can be used in a formula by enclosing the dimension ID inside [d/ ] [d/PRODUCT]
[d/DIMENSION_IDENTIFIER].[p/ATTRIBUTE] A dimension attribute can be used in a formula by adding a suffix to the dimension identifier, starting with .[p/ and ending with ] [d/PRODUCT].[p/Description]
[d/DIMENSION_IDENTIFIER].[h/HIERARCHY_IDENTIFIER] Results can be calculated for only a particular hierarchy, by adding a suffix to the dimension identifier, starting with .[h/ and ending with ] [d/PRODUCT].[h/ALL_PRODUCTS]
In the Modeler: ['VARIABLE_IDENTIFIER']

In stories: [@VARIABLE_IDENTIFIER]

A variable can be used in a formula by enclosing the variable identifier inside [' '] (in the Modeler) or inside [@ ] (in stories) ['PRODUCT_VARIABLE'][@PRODUCT_VARIABLE]
[MODEL_IDENTIFIER] For LINK formulas only: the first parameter is the model identifier, which can be specified by enclosing the model ID inside [ ] [BusinessPlanning:BusinessPlanning]

Procedure

These are the steps you'd follow to enter a formula in the Modeler:

  1. In the Modeler, open a model and select the account dimension.
  2. Scroll to the right until you see the Formula column.
  3. Select the cell or row in the Formula column where you want to add a formula.
  4. In the Member Details panel, if you're already familiar with formula syntax, you can begin typing your formula in the Formula field.

    As you begin to type, a hint list shows all available options (including formula and account members) that match the text you've typed. The list shows values for both account member IDs and account member descriptions.

    Instead of typing formulas manually, you can press Ctrl + Space to choose from a list of values that are valid for that location in the formula, or type [ for a list of valid measures and dimensions.

    If you select a formula from the hint list, a template is automatically entered in the formula entry bar. A short help text description is displayed when you move the mouse pointer over the template.

    Some formulas available in the Modeler, for example YoY (year over year), take parameters. Press Ctrl + Space to choose from a list of values that are valid for that location in the formula.

  5. If you're not very familiar with formula syntax, you can select Open the advanced formula tool to help you to enter, format, and validate your formula.

    The formula editor lists all the available functions (functions, conditions, operators) that can be used to build up a formula. You can select the functions you need from the lists and also type additional values in the editor.

    In the Modeler, the formula elements are color-coded as follows:

    Note
    The formula editor is available only if a formula can be defined for that account. For example, for analytic-type HANA models, you can't define formulas for existing measures.