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:


Visual	Script
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.

A few functions are only available in the script editor, and not supported in the visual tool:

CARRYFORWARD
ATTRIBUTE
ELIMMEMBER
Mathematical functions MOD and POWER
Conversion functions (ROUND, FLOOR, CEILING, and TRUNC)
Time calculation functions (DAY, MONTH, YEAR, PERIOD, DAYSINMONTH, DAYSINYEAR, DATERATIO)
CONFIGURATION.HIERARCHY, CONFIGURATION.TIME_ZONE_OFFSET
ELSEIF, ELSE
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.

Using Parameters

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.

Unsupported Functions

Most functions support measures, except the following:

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

Measures have specific behavior in these cases:

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
```

Several restrictions apply to using measures with AND or OR operators. See About Script Formulas and Calculations in Advanced Formulas for Planning for details.

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.

For example, for a classic account model that has the same set of dimensions as a target measure model, a simple link function that copies account values to a measure looks like this:

Sample Code

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.

When the source model uses a private dimension, you can create a private dimension with the same structure and members, and map it in the LINK statement. For example:

Sample Code

LINK([AccountModel], [d/Version]=”public.Forecast”, [d/Account]=”Quantity”,[d/TargetModel_CostCenter]=[d/SourceModel_CostCenter])

See About Script Formulas and Calculations in Advanced Formulas for Planning for details about LINK statements.

Note

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