Using the Visual Editor to Create an Advanced Formula Data Action

Planning requires various types of calculations. To perform these calculations, you can use advanced data actions (formulas).

Here are some key points regarding advanced formulas:

• Use Cases:

  • Advanced formulas provide a mechanism for custom calculation logic
  • Driver-based formulas: Rev = Price x Qty
  • Depreciation calculation
  • Populate opening balances for headcounts
  • Mark-ups | Mark-downs
  • Schedule or run on an ad-hoc basis

• Maintenance:

  • The script editor provides context help and code completion
  • Validation and trace mode in the script editor

• Features:

  • The visual editor provides a graphical wizard to define calculation logic
  • The statements are represented by boxes, which can be further configured with special dialogs
  • The graphical representations give users a clear logic flow

Use the visual tool to drag and drop graphic elements in a flow diagram to create advanced formulas. The general process flow for creating advanced formulas is:

1. Specify the context
2. Define and use a variable if needed
3. Set the configuration options: Calendar, Signflip, Unbooked
4. Formula options:
   • Calculation
   • Repeat
   • Condition
   • Delete
   • Comments

• Support AND/OR for IF condition: Complex and detailed condition expressions are possible with shorter scripts, which means better execution performance time as well as better readability.

• Keyword "BREAK" that stops a loop if a specific condition is fulfilled: Support an immediate stop of FOREACH loop with IF condition or others. For example, stopping the depreciation loop with date dimension is possible if the residual book value reaches zero value or the iteration number reaches and the same as the useful life. You don't need to loop for an entire date scope resulting in better execution performance.

• Load pre-aggregated value as calculation scope: Minimize the dataset size of calculation scope for non-calculation related dimensions.
• Pop-up dialog-box that shows the calculation scope: Estimate the calculation scope at design time and optimize the script for better execution performance.

Membersets are used to restrict the data region for the calculation.

Typical use cases:

• MEMBERSET [d/ORGANIZATION].[p/REGION] ="ASIA": Set the scope of the Organization dimension's Region property to ASIA.
• MEMBERSET [d/FLOW] = ("F_TEMP", "F_NONE"): Set the scope of the Flow dimension to the F_TEMP and F_NONE members.
• MEMBERSET [d/CURRENCY] = [d/ORGANIZATION].[p/CURRENCY]: Set the Currency dimension's scope to the value of the Organization dimension's currency property.
• CONFIG.HIERARCHY = [d/ENTITY].[h/H1] MEMBERSET [d/ENTITY] = BASEMEMBER([d/ENTITY], "World"): Return all base members of the member "World" in hierarchy H1 and set these base members as the member scope of the Entity dimension.
• MEMBERSET [d/DATE] ="202201" TO "202212": Set the scope of the Date dimension from 202201 to 202212.

Memberset is used to determine the context, that is the Point of View or the Scope.

In the preceding example, see the explanations for the script:

• MEMBERSET [d/Date] = (BASEMEMBER([d/Date].[h/YQM], %YearMonth%))

  BASEMEMBER refers to leaf members for calendar year in this example.

  The time hierarchy is set to the Calendar Year, therefore YQM is the hierarchy ID.

  % denotes variables: for example %YearMonth% refers to the prompt/variable for time.

  Result: If you select 2021, the system will include all months of 2021 in the calculation.

• MEMBERSET [d/Cost_Center] = (BASEMEMBER([d/Cost_Center].[h/H1], "All_Cost_Centers"))

  The H1 hierarchy for the cost center dimension has been selected.

  All_Cost_Centers is the id of the top node/parent.

• Result, if the user selects hierarchy node Canada, only Canadian cost centers will be included in the calculation.

The following script will only run for PC1 & PC2 (that is, only PC1 & PC2 are scoped).

In the preceding example, January data is being copied to February with an uplift:

DATA([d/Date] = %TargetMonthYr%) = RESULTLOOKUP([d/Date]=PREVIOUS(1)) * %UpLift%

• In this example, %TargetMonthYr% is the receiver or target that is dynamically selected by the planner.
• RESULTLOOKUP([d/Date]=PREVIOUS(1)) dynamically reads the previous month (or period) as compared to the %TargetMonthYr% selection.
• %UpLift% is the variable for the uplift provided by the planner. Example: 1.25.

The visual editor can be used for simpler calculations. It generates the script automatically.

The script editor is used for more complex programming.

You can hover on a dimension in the data line to see what members are included in the scope of the Calculation.

There are two different ways to design advanced formulas – using the visual tool and writing scripts.

You can build your own logic in the visual editor and then view the generated code in the script editor.