Add Advanced Formulas to Your Data Action
Advanced formulas allow you to design formulas of transformations and calculations to apply to the source data and write the data to defined scopes of the target model.
There are two different ways of designing advanced formulas – using the visual tool and writing scripts. Listed below are the differences between the two approaches:
|Target business users
|Target professional modeling users, such as IT specialists
|No coding skills are required
|Requires basic coding knowledge
|Created by dragging and dropping graphic elements
|Created by writing scripts
Simply put, the visual tool allows business users with no expertise in programming to design formulas by dragging and dropping rather than directly writing scripts, thus helping them quickly set up a simple business case. However, the tool isn't a replacement for script-based advanced formulas – scripting should remain the primary tool to define and design complicated business scenarios.
Mathematical functions MOD and POWER
Conversion functions (ROUND, FLOOR, CEILING, and TRUNC)
Time calculation functions (DAY, MONTH, YEAR, PERIOD, DAYSINMONTH, DAYSINYEAR, DATERATIO)
Some of the MEMBERSET functions
Dimension aggregation settings (AGGREGATE_DIMENSIONS, AGGREGATE_WRITETO, MODEL)
Also, the visual editor has a few more restrictions on the use of external parameters, as well as conditional filters using IF statements. If you convert these functions to the visual editor, they will be displayed as read-only grey blocks. For more details, refer to Display of the Unsupported Script in the Visual Tool.
If you need to define a complex scenario with these features, it's best to use the script editor.
You might want to quickly update a value or change a member or measure in several different places throughout advanced formulas, or allow a user to set their own values in prompts when they run the data action. In this case, you can use parameters for those values.
Before that, you need to first create parameters in the data action of your advanced formulas. To learn how to create parameters, see Add Parameters to Your Data Actions and Multi Actions.
Then, in both advanced formulas created via script and via the visual tool, you can choose a member or measure parameter instead of a dimension member or measure, or a number-type parameter instead of a number or number variable. In advanced formulas created via scripts, besides the member selector, you can enter % to view a list of all available parameters.
Using Advanced Formulas with a Model with Measures
In a model with measures, you can add measures to your advanced formulas. They usually behave similarly to dimension members.
You can specify the measures structure by selecting Measures in the visual editor or by typing [d/Measures] in the script editor, and specify individual measures in scripts using quotation marks, for example "Amount".
Like dimensions, the calculation scope needs to include at least one measure. If you don’t define specific measures in the calculation scope, all measures are included.
FOREACH and FOREACH.BOOKED
AGGREGATE_DIMENSIONS (measures can’t be aggregated)
Any function requiring a hierarchy or attribute, since measures don’t have either. For example, CONFIG.HIERARCHY, ATTRIBUTE(), ELIMMEMBER(), and time functions.
Functions with Specific Behavior for Measures
LINK: If the source model has measures, you need to set a single source measure in the LINK statement.
Condition clauses (IF and ELSEIF): When you include a RESULTLOOKUP() function in a condition, you need to set a measure, for example:Sample Code
IF RESULTLOOKUP([d/Measures] = "Price", [d/Product_Group] = "AC") > 1000 THEN //Statement… ENDIF
Copying Data from a Classic Account Model to the New Model Type
You may need to copy data from a classic account model to a model with measures, for example, if you want to use a model with measures but can’t migrate your classic account model yet.
Cross-model copy steps don’t support copying between classic account models and models with measures, but you can use an advanced formula step with a LINK function instead.
DATA([d/Measures]=”Quantity”) = LINK([AccountModel], [d/Version]=”public.Forecast”, [d/Account]=”Quantity”)
Copying model data is easiest when the source and target models use as many of the same public dimensions as possible. This lets you create a LINK statement without setting a default member or dimension property for those dimensions.
LINK([AccountModel], [d/Version]=”public.Forecast”, [d/Account]=”Quantity”,[d/TargetModel_CostCenter]=[d/SourceModel_CostCenter])
The absolute values of accounts will be copied by default. If you want to copy negative values for accounts that use automatic sign flipping, set the configuration: CONFIG.FLIPPING_SIGN_ACCORDING_ACCTYPE=ON
- Get Started with Data Actions for Planning
- Understand General Rules for Advanced Formula Calculations for Planning
- Optimize Advanced Formulas for Better Performance
- About Script Formulas and Calculations in Advanced Formulas for Planning
- Write Business Scenarios for Planning with Advanced Formulas Actions