Using the Script Editor to Create an Advanced Formula Data Action

Variables in advanced formulas can be used to store data temporarily while the script is running, to avoid calculating the value repeatedly. This is the traditional concept referred to as a Local Variable or Virtual Variable Member. The virtual variable member will be stored independently and cannot be added to a dimension like a real dimension member. After defining a variable member for a certain dimension, you can use the variable member to store values only for that specific dimension.

1. Write the definition after the configuration settings and before all the instructions.
2. Create a new name for a virtual variable member. Make sure that in the variable member definition it always starts with the prefix "#".
3. Dimension Name is mandatory and must be an existing dimension in the current data action model. The version dimension is not supported. Measures are supported, for example: VARIABLEMEMBER #Total_Revenue OF [d/Measures]
4. A virtual variable member can only be used in DATA and RESULTLOOKUP functions. When inputting "#" in DATA() or RESULTLOOKUP(), a list of all available virtual members will be shown.

In the following example of advanced logic virtual variables, you can see that:

• SumOfProducts is the temporary account for total sales revenue.
• Since the total sales are over 1000 (1500), the system calculates other income of 1500 x -.01 for each product.

Time can be interpreted as YQM: Year | Quarter | Month or as FYQP: Fiscal Year | Quarter | Period.

This configuration governs the time hierarchy of the script. If this configuration is not defined, the default time hierarchy from the model is used.

In advanced formulas, all data is calculated in absolute value by default, regardless of the account type. This is important when working with the Account dimension, where certain accounts will have positive values while others will have negative values. If you want to calculate with the existing sign-flipping rules of Account dimensions, then enter CONFIG.FLIPPING_SIGN_ACCORDING_ACCTYPE=ON at the beginning of your script.

In the following example, the memberset command is selecting data for revenue and cost and then writing the result to retained earnings. If the sign flip setting is ON, the correct value (40) is written to retained earnings.

In the following example, January data for profit centers PC1/2/3 is being copied to February. By using the Unbooked setting, the existing 500 value for PC4 can either be retained or set to zero (to match the source).

• [ ]: Dimensions, properties, hierarchies are enclosed in square brackets
• d: Dimension. Example: [d/Cost_Center]
• p: Property. Example: [d/Account].[p/ElimTarget]
• h: Hierarchy. Example: [d/Cost_Center].[h/H1], "All_Cost_Centers"))
• %: Variables. Example: %YearMonth%
• #: Unassigned. Example: since labor hours aren’t currency related, currency is unassigned:RESULTLOOKUP([d/Account] = "LHours", [d/Currency] = "#")
• " ": Double quotes are required when specifying dimension members.
• /: Comments

For more syntax options, to the reference guide: Advanced Formulas Reference Guide

As you type, a hint list shows all available functions that match the text you've typed. A short help text description is available for each function. You can use Ctrl+Space to choose from a list of values that are valid for that location in the formula, or type [d/ for a list of valid dimensions and properties to use in the formula.

There are also formula templates for common calculations. If you select a formula from the hint list, a template is automatically entered in the formula entry bar.

Examples of how to use auto-complete:

1. Type M to access the command that start with the letter M.
2. Type [d/ to access the model dimensions.
3. Press Ctrl+Space to access members and parameters.

When you create an advanced formula, you have 5 options.

Calculation will create an equation such as Target = Expression

A calculation is divided into two parts: Target and Source.

• Target is the data range to which you plan to write data.
• Source is the data involved in the calculation.

Delete will prompt for a filter upon which to identify which cost center to delete, for example.

Repeat (loop) will prompt for a dimension to loop over, and then you can add a calculation, for example.

For example, calculate revenue amount with a 10% increase over the previous month:

123456
MEMBERSET [d/Date] = (BASEMEMBER([d/Date].[h/YQM], "[2024]"))
MEMBERSET [d/Measures] = ("Amount")
MEMBERSET [d/Account] = ("Revenue")
FOREACH [d/Date]
Data() = ResultLookup([d/Date]=PREVIOUS(1)) * 1.1
ENDFOR 

Comment creates an input field for explanations.

Condition will prompt for a filter upon which to create the if statement, and then you can add a calculation.

For example, if the company = US, then copy prior year data to the current year data +10%.

123456
MEMBERSET [d/Date] = (BASEMEMBER([d/Date].[h/YQM], "[2024]"))
MEMBERSET [d/Measures] = ("Amount")
MEMBERSET [d/Account] = ("Revenue")
IF [d/Company] = "US" THEN
DATA() = RESULTLOOKUP([d/Date] = Previous(12)) * 1.1
ENDIF

In the previous examples, you can see how the system creates formulas depending on which option you pick. Each one generates code that you can view in the script dialog.

In the following example, you can see an example of a formula used to calculate labor in the visual editor. You can also see the editing options available.

When using script to write advanced formulas, keep the following in mind:

1. Each line of instruction inherits the calculation results of previous lines of instructions.
2. Advanced formulas perform all calculations only with leaf-level members that don’t have any account formulas defined.
3. In a RESULTLOOKUP function, the function considers the most detailed values in the dataset, even if the formula doesn't specify them. For example, consider the following record set:

   Account	Product	Entity	Date	Value
   Quantity	Sparkling Water	Region	Dec.2018	210 (aggregate of all countries)
   Quantity	Sparkling Water	Germany	Dec.2018	50
   Quantity	Sparkling Water	France	Dec.2018	70
   Quantity	Sparkling Water	UK	Dec.2018	90

   You write a formula to add 20 to the Quantity Values for December 2018 and write the new values to January 2019:

   MEMBERSET[d/Product]="Sparkling Water" MEMBERSET[d/Account]="Quantity" DATA([d/Date]="Jan.2019")=RESULTLOOKUP([d/Date]="Dec.2018")+20

   The final result will add 20 to each detailed Entity value, not to the aggregated Region value:

   Account	Product	Entity	Date	Value
   Quantity	Sparkling Water	Region	Jan.2019	270 (aggregate of all countries)
   Quantity	Sparkling Water	Germany	Jan.2019	70
   Quantity	Sparkling Water	France	Jan.2019	90
   Quantity	Sparkling Water	UK	Jan.2019	110

4. A null value is treated as a non-existent value, while 0 is treated as an actual number. If the RESULTLOOKUP function returns a null value, advanced formulas do nothing.
5. Advanced formulas basically work with the fact record-set of the model. If you don't specify other dimension information and members via the MEMBERSET definition, the advanced formulas will take all members from the dimensions in the model.