Design Advanced Formulas Using Scripts

You can write a script of transformations and calculations to create advanced formulas.

Prerequisites

Create a data action before adding an advanced formulas step to it.

Detailed Steps

  1. In a Data Action page, select (Add Advanced Formulas Step).

  2. Type a Name and Description for the advanced formulas step.

  3. In the formula designer section, choose the tab Script.

    A template of advanced formulas is displayed by default.

  4. Before writing advanced formulas in the body, we recommend you first familiarize yourself with the general rules and the tips that can enhance your script's performance. For details, refer to Understand General Rules for Advanced Formula Calculations for Planning and Optimize Advanced Formulas for Better Performance.

  5. Begin typing your script in the body.

    As you start to type, a hint list shows all available functions that match the text you've typed. A short help text description is automatically displayed for each function.

    For more information about available advanced formulas and use cases, refer to About Script Formulas and Calculations in Advanced Formulas for Planning. To learn about the syntax and semantics of advanced formula scripts, check out the SAP Analytics Cloud Advanced Formulas Reference Guide.

  6. Formula Templates: If you select a formula from the hint list, a template is automatically entered in the formula entry bar.

  7. To add a single line of comment, you can start the comment with //. To add multiple lines of comments, you can enclose the comments in a block start by /* and end with */.

  8. To format your script, select the Format button.

    After the 2020.18 release of SAP Analytics Cloud, your scripts will automatically apply monospace font instead of the previous proportional font. If you still want to use the old format, you can select the (Undo) button to revert the font style changes.

  9. After you complete the script, select the Validate button to validate the script. All the lines with errors will be highlighted. Select the error messages at the bottom of the page, and you'll jump to the corresponding line containing that error.

  10. If there are no errors in the script, you can check the calculation scope for specific functions. This helps you get good performance and correct calculation results in an advanced formulas step.

    Check the scope by hovering over one of the supported functions.

    The Estimated Function Scope window shows how many members are included for each dimension, and where the scope was restricted:

    The Estimated Function Scope shows the number of dimension members included in a function's scope.
    Checking estimated function scope

    For details about checking the estimated function scope and the supported functions and types of filters, see Optimize Advanced Formulas for Better Performance.

  11. Once you are familiar with all the functions and want to hide the hints, the colors that indicate different types of objects, or the estimated function scope, select the button  and deselect Help, Highlight, or Estimated Function Scope.