Using the Visual Editor to Create an Advanced Formula Data Action

Planning requires various types of calculations. An advanced formula step in a data action allows you to design formulas for calculations to apply to the source data. The results are written to specified scopes in the planning model.

Unlike dimension formulas, which perform the calculation in real-time, a data action with an advanced formulas step stores the result of the calculation in the SAP Analytics Cloud database. And, as with dimension formulas, parameters can be used in the advanced formula data action.

If there's a need to perform calculations that aren't stored in the database, there are several options that you can use:

• Data table formulas.
• Story-based calculated measures.
• Model-based calculated measures.

When should you consider a data action with an advanced formulas step? Some use cases include:

• When custom calculation logic is required.
• For driver-based formulas such as Revenue = Price x Quantity.
• For depreciation calculations.
• To populate opening balances for headcounts.
• For mark-ups and mark-downs.
• When they need to be scheduled or run on an ad-hoc basis.

Key features of advanced formula steps include:

1. 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.
2. 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.
3. Load pre-aggregated value as calculation scope: Minimize the dataset size of calculation scope for non-calculation related dimensions.
4. Pop-up dialog-box that shows the calculation scope: Estimate the calculation scope at design time and optimize the script for better execution performance.

There are two ways to generate scripts in a data action that contains a advanced formulas step:

1. The visual editor is graphical wizard to define calculation logic in a flow diagram. The statements are represented by boxes, which can be further configured with special dialogs, giving a clear visual logic flow. Simple scripts are generated that are based on the visual build and can be viewed by clicking Script.
2. The script editor provides context help and code completion and includes validation and trace mode. It is used for more complex programming.

In summary:

In this lesson, we focus on the visual editor as a way to create advanced formulas. We will use the visual editor to create a simple calculation to calculate labor hours. In the next lesson, we take a more in-depth look at variables, configuration options, formula options, and script syntax.

In the visual editor, you can:

1. Specify the context.
2. Add a variable, if required.
3. Set any required configuration options.
   • Time hierarchy - fiscal year or calendar year
   • Signflip - on or off
   • Unbooked - on or off
4. Choose the required formula option.
   • Calculation
   • Repeat
   • Condition
   • Delete
   • Comment

Membersets are often used to restrict the data region for the calculation. They are set in the Context section of the visual editor, or written directly into the script editor.

Typical use cases for membersets in an advanced formula step include:

• MEMBERSET [d/ORGANIZATION].[p/REGION] ="ASIA": Sets 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.

Let's explore the advanced formula script for the following example:

• 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, then 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 you select the hierarchy node Canada, then only Canadian cost centers will be included in the calculation.

The following script will only run for Profit Center 1 and Profit Center 2 (that is, only PC1 & PC2 are scoped). In the story, January data is copied to February with an uplift using the following:

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

• %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.