Understand General Rules for Advanced Formula Calculations for Planning

Read this topic to familiarize yourself with the general behaviors of advanced formulas.

Four Things You Need to Know Before Getting Started

  1. Advanced formulas is a type of data action. Data action steps are executed sequentially, and the next data action step is calculated based on the result of the previous one.

    Example

    For instance, there are three steps from top to bottom – A, B, and C. If the calculation scope of these steps overlap each other, step B will start the calculation based the result of step A, and step C will base its calculation on that of step B. These step results are all intermediate calculation results, and the data is published to a corresponding version, either private or public, after all the data action steps are completed.

  2. In advanced formulas, each line of instruction inherits the calculation results of previous lines of instructions.

    For example, if a returns 100 and b returns 5 and you write the script below:

    DATA (b) = RESULTLOOKUP (a)

    DATA (c) = RESULTLOOKUP (b) * 2

    Then it returns a calculation result 200 for c, but not 10. Because the value of b which is overwritten by 100 in the first line of instruction is inherited by the second line of instruction.

  3. Advanced formulas performs all calculations only with leaf-level members that don’t have any account formulas defined. Any other members that have child members are not supported at this time.

  4. In a RESULTLOOKUP function, values of different data slices returned by RESULTLOOKUP are not aggregated (unless you explicitly define them in an AGGREGATE_DIMENSION function).

    For example, to copy 2018 December sales quantity of Sparkling Water to 2019 January and increase it by 20, write the script below:
    Sample Code
    MEMBERSET[d/Product]="Sparkling Water"
    MEMBERSET[d/Account]="Quantity"
    
    DATA([d/Date]="Jan.2019")=RESULTLOOKUP([d/Date]="Dec.2018")+20

    In this case, the RESULTLOOKUP function returns the following record set:

    Account

    Product

    Entity

    Date

    Value

    Quantity

    Sparkling Water

    Germany

    Dec.2018

    50

    Quantity

    Sparkling Water

    France

    Dec.2018

    70

    Quantity

    Sparkling Water

    UK

    Dec.2018

    90

    In SAP Analytics Cloud, even though you didn't define Entity dimension in your script, 20 is added respectively to each slice of result and returns the intermediate calculation results below:

    Account

    Product

    Entity

    Date

    Value

    Constant

    Result Value

    Quantity

    Sparkling Water

    Country

    Dec.2018

    210 (aggregated value)

    270 (aggregated value)

    Quantity

    Sparkling Water

    Germany

    Dec.2018

    50

    20

    70

    Quantity

    Sparkling Water

    France

    Dec.2018

    70

    20

    90

    Quantity

    Sparkling Water

    UK

    Dec.2018

    90

    20

    110

    However, in other languages, such as FOX, a similar script will first aggregate the value on other dimensions not defined in the script, which in this case is Entity and return a sum value of the sales quantity of Sparkling Water in 2018 December. Then add 20 to the sum value. From the result below you can find that the total calculated value of different entities is 230, while the total value returned by the script you write in SAP Analytics Cloud is 270.

    Account

    Product

    Entity

    Date

    Value

    Constant

    Result Value

    Quantity

    Sparkling Water

    Country

    Dec.2018

    210

    20

    230

    To get similar results in an advanced formula, you can use the AGGREGATE_DIMENSION function:
    Sample Code
    AGGREGATE_DIMENSIONS[d/Entity] 
    AGGREGATE_WRITETO[d/Entity]= "#" 
    MEMBERSET[d/Product]="Sparkling Water" 
    MEMBERSET[d/Account]="Quantity" 
    
    DATA([d/Date]="Jan.2019")=RESULTLOOKUP([d/Date]="Dec.2018")+20

    In this case, the formula writes 230 to the unassigned member (#) of the entity dimension for Jan 2019.

Calculation Scope

The base scope of your advanced formulas step includes all combinations of leaf members and measures from the entire model, excluding calculated members and measures.

For example, consider a model with the following measures and dimension members:

Dimension Number of Members
Measures 5
Account 200
SalesOffice 150
Product 48
Date 120
Total combination 864,000,000

The total combination is found by multiplying all the numbers together.

Consider running a single line of script without any MEMBERSET statements:

DATA() = 100

This script writes 100 to all combinations of leaf members and measures in the model, which is 864 million.

Levels of Scope

You can reduce the scope of your calculations at a few different levels. From broadest to most specific, these include:
  • Memberset scope

  • Condition scope

  • Statement scope

As your scope gets more specific, it will override the broader scope. The scopes do not need to intersect, for example, if you set the account scope to Salaries in a MEMBERSET, you can still change it to Onboarding Expenses in an IF condition.

Memberset Scope

MEMBERSET statements let you set a scope that applies across the entire step, except where more specific scopes are defined. In effect, each MEMBERSET statement replaces the base scope for that dimension.

In the following script, the DATA() function only includes 7200 cells in its scope (1 measure, 1 account, 150 sales offices, 4 products, and 12 months):
Sample Code
MEMBERSET [d/Measures] = (“Amount”)
MEMBERSET [d/Account] = (“UnitSold”)
MEMBERSET [d/Account] = (“UnitSold”)
MEMBERSET [d/Product] = (“Barrel1”, “Barrel2”, “Barrel3”, “Barrel4”)
MEMBERSET [d/Time] = BASEMEMBER([d/Time].[h/YQM], “2021”)

DATA() = 100

Condition Scope

Condition scopes are applied in IF statements and FOREACH functions.

IF statements let you define filters for specific dimensions. Regardless of the MEMBERSET scope, these filters will apply between the IF keyword and the corresponding ENDIF keyword.

A FOREACH function lets you repeat a calculation for each leaf dimension member in its specified dimension, or each combination of leaf members if you specify more than one dimension. Therefore, each iteration of the calculation runs on a single member of the associated dimensions. (The number of iterations can be reduced by IF and MEMBERSET functions on the same dimensions. See Optimize Advanced Formulas for Better Performance for examples.)

Statement Scope

Each statement in your script that has dimensions associated with it can also set its own scope by specifying members for those dimensions. For example:

Sample Code
DATA([d/Account]= “UnitSold”) //Account:1
RESULTLOOKUP([d/Time] = “202102”, [d/Product] = “Barrel1”) //Account:1, Product:1
For other dimensions, individual statements can inherit their scope from MEMBERSET and IF statements. For example:
Sample Code
MEMBERSET [d/Measures] = (“Amount”)
MEMBERSET [d/Product] = (“Barrel1”, “Barrel2”, “Barrel3”, “Barrel4”)
MEMBERSET [d/Time] = BASEMEMBER([d/Time].[h/YQM], “2021”)

DATA([d/Account]= “UnitSold”) = 100 //Measures: 1, Account:1, Product:4, SalesOffice:150, Time:12 = 7,200
IF [d/SalesOffice] = “InterCompany_Export” THEN
    DATA([d/Account] = “UnitSold”) = 0 // Measures: 1, Account:1, SalesOffice:1, Product:4, Time:12 = 48
ENDIF
IF ([d/Account] = “UnitReturn” AND [d/SalesOffice] = “InterCompany_Export”) THEN
    DATA() = // Measures: 1, Account:1, SalesOffice:1, Product:4, Time:12 = 48
    RESULTLOOKUP(([d/Account] = “UnitSold”, [d/SalesOffice] = “Depot”) // Measures: 1, Account:1, SalesOffice:1, Product:4, Time:12 = 48
ENDIF

You can check the calculation scope of certain functions in the script editor. See Check the Calculation Scope for details.

General Rules

Rules of Handling Null and 0

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. Here are some examples:

  1. DATA(c)=RESULTLOOKUP(a) + RESULTLOOKUP(b)

  2. DATA(c)=RESULTLOOKUP(a) - RESULTLOOKUP(b)

  3. DATA(c)=RESULTLOOKUP(a) * RESULTLOOKUP(b)

  4. DATA(c)=RESULTLOOKUP(a) / RESULTLOOKUP(b)

In the cases above, when RESULTLOOKUP(b) returns a null value, the action results of the advanced formulas are as follows:

  1. DATA(c)=RESULTLOOKUP(a)

  2. DATA(c)=RESULTLOOKUP(a)

  3. DATA(c)=Null. The advanced formulas generates no data.

  4. DATA(c)=Null. The advanced formulas generates no data.

In the cases above, if RESULTLOOKUP(b) returns a value 0 instead of null, the action results of the advanced formulas are as follows:

  1. DATA(c)=RESULTLOOKUP(a)

  2. DATA(c)=RESULTLOOKUP(a)

  3. DATA(c)=0. Means a 0 value is created for c.

  4. DATA(c)=Null and no run time error. This means the advanced formulas don’t perform any actions and no data is created.

If RESULTLOOKUP(a) returns 0 and RESULTLOOKUP(b) returns a positive value, the formula generates the results below.

  1. DATA(c)=RESULTLOOKUP(b)

  2. DATA(c)= -RESULTLOOKUP(b)

  3. DATA(c)=0. Means value 0 is created for c.

  4. DATA(c)=0. Means value 0 is created for c.

Rules of Cell Value Filter

The cell value filter, in the form of a value comparison, can be used with RESULTLOOKUP in an IF instruction.

Example: IF RESULTLOOKUP([d/ACCOUNT]="QUANTITY") > 10 THEN

In the two cases below, you can see how a cell value filter and a calculation range work together in advanced formulas.

Case1: There is an intersection between the calculation range and the cell value filter range

Sample Code
MEMBERSET [d/ACCOUNT]=("Sales")
MEMBERSET [d/PRODUCT]=("16GB", "32GB")
MEMBERSET [d/DATE]=("201801")

IF ResultLookup()>0 THEN
    Data()=ResultLookup([d/DATE]=PREVIOUS(12))
ENDIF

In this example, the cell value filter part is defined by "IF RESULTLOOKUP() > 0".

The calculation range part is defined with MEMBERSET definitions.

First, the cell value filter further narrows down the calculation range. By adding the RESULTLOOKUP () > 0 condition, the value null of (Sales, 16GB, 201801) is dropped from the calculation range because it's not greater than 0. Then the DATA () will have only the remaining value, 100 of (Sales, 32GB, 201801), as the final calculation scope.

Case2: There is no intersection between the calculation range and the cell value filter range

Sample Code
MEMBERSET [d/ACCOUNT]=("Sales")
MEMBERSET [d/PRODUCT]=("16GB", "32GB")
MEMBERSET [d/DATE]=("201801")

IF ResultLookup([d/ACCOUNT]="Cost") < 20 THEN
    Data()=ResultLookup([d/DATE]=PREVIOUS(12))
ENDIF

In this example, the cell value filter part is defined by "IF RESULTLOOKUP ([d/ACCOUNT]="Cost") < 20".

And the calculation range part is defined with MEMBERSET definitions.

The cell value filter tries to find the range. But in this case, MEMBERSET function only defines the Sales member of the Account dimension, while RESULTLOOKUP in the IF condition checks only the Cost member of the Account dimension. There is no intersection between the calculation range and the cell value filter range.

So, other conditions that met the cell value filter will be applied. In this case, 201801 16G is added.

The intersection between the calculation range and the new combination of the cell value filter range, which is 201801 16G Sales, will be the final default scope used in the DATA () function.

Calculation Rules

Calculation Based on Fact Record-Set

Advanced formulas basically works with the fact record-set of the model. Here is an example:

Sample Code
MEMBERSET [d/Product] ="A"
DATA([d/Account] ="Revenue")=RESULTLOOKUP([d/Account]="Price") * RESULTLOOKUP([d/Account]="Quantity")

The example above multiplies the sales quantity and price of product A for all time periods. If the user does not specify other dimension information and members via the MEMBERSET definition, the advanced formulas will take all members from these dimensions.

Below is another example of calculating Q4 revenue of global company A. Company A sells soft drinks to the European region. Company A decides its sales planning for 2018 Q4, with basic price of product and sales quantities planned as follows.

Account

Product

Entity

Date

Value

Price

Sparkling Water

*

Oct.2018

500

Price

Sparkling Water

*

Nov.2018

600

Price

Sparkling Water

*

Dec.2018

700

Quantity

Sparkling Water

Germany

Oct.2018

50

Quantity

Sparkling Water

Germany

Nov.2018

70

Quantity

Sparkling Water

Germany

Dec.2018

90

Quantity

Sparkling Water

France

Oct.2018

30

Quantity

Sparkling Water

France

Nov.2018

50

Quantity

Sparkling Water

France

Dec.2018

80

Then company A can calculate the revenue value of 2018 Q4 with an advanced formula:

Sample Code
MEMBERSET [d/Date]=("201810","201811","201812")

DATA([d/Account] ="Revenue")=RESULTLOOKUP([d/Account] ="Price",[d/Entity]="*") * RESULTLOOKUP([d/Account]="Quantity")

In the above formula, the first RESULTLOOKUP function returns the record set below:

Account

Product

Entity

Date

Value

Price

Sparkling Water

*

Oct.2018

500

Price

Sparkling Water

*

Nov.2018

600

Price

Sparkling Water

*

Dec.2018

700

And the second RESULTLOOKUP function returns the record set below:

Account

Product

Entity

Date

Value

Quantity

Sparkling Water

Germany

Oct.2018

50

Quantity

Sparkling Water

Germany

Nov.2018

70

Quantity

Sparkling Water

Germany

Dec.2018

90

Quantity

Sparkling Water

France

Oct.2018

30

Quantity

Sparkling Water

France

Nov.2018

50

Quantity

Sparkling Water

France

Dec.2018

80

Then, multiplying the data in above two record sets will return the intermediate record set below:

Account

Account

Product

Entity

Date

Value (Price)

Value (Quantity)

Result

Price

Quantity

Sparkling Water

Germany

Oct.2018

500

50

25000

Price

Quantity

Sparkling Water

Germany

Nov.2018

600

70

42000

Price

Quantity

Sparkling Water

Germany

Dec.2018

700

90

63000

Price

Quantity

Sparkling Water

France

Oct.2018

500

30

15000

Price

Quantity

Sparkling Water

France

Nov.2018

600

50

30000

Price

Quantity

Sparkling Water

France

Dec.2018

700

80

56000

At last, in the DATA function calculation, these records are copied to the Revenue Account member and new records are generated via the DATA function as below:

Account

Product

Entity

Date

Value

Revenue

Sparkling Water

Germany

Oct.2018

25000

Revenue

Sparkling Water

Germany

Nov.2018

42000

Revenue

Sparkling Water

Germany

Dec.2018

63000

Revenue

Sparkling Water

France

Oct.2018

15000

Revenue

Sparkling Water

France

Nov.2018

30000

Revenue

Sparkling Water

France

Dec.2018

56000

New Data Creation – Record based vs. Master data based

Advanced formulas basically create new data on the source record based. For example, a user wants to copy data from the past year to current year.

Sample Code
MEMBERSET [d/Date]=BASEMEMBER([d/Date]. [h/YQM],"2018")

DATA([d/Audit]="LogicCalc")=RESULTLOOKUP([d/Audit]="Input",[d/Date]=PREVIOUS(12))

The formula above copies 2017 input data to 2018 with the "LogicCalc" audit flag. The DATA function will have a date scope from 2018 Jan. to 2018 Dec. according to the MEMBERSET definition.

But if the source scope 2017 has data only from 2017 Jan. to 2017 Sep., then the DATA function will copy the existing source data range only. No 2017 data is copied to the time range 2018 Oct. to 2018 Dec. Yet in some cases, you might want to overwrite all existing data in 2018 and initiate it as value 0 if there are no data in the source data range. Then you can choose to enable the function CONFIG.GENERATE_UNBOOKED_DATA. As a result in this example, from 2018 Oct. to 2018 Dec., the value will be overwritten by 0.

For more information about this option, see CONFIG.GENERATE_UNBOOKED_DATA.

Sign Value and Sign-Flipping

In planning models, there are four financial types of account members: AST, LEQ, INC, EXP. Each account type is created with a predefined sign value. But in advanced formulas, all data are calculated with absolute value by default, regardless of the account type. For example,

Sample Code
DATA([d/Account] ="Revenue")=RESULTLOOKUP([d/Account]="Income") + RESULTLOOKUP([d/Account]="Expense")

In this example, if the total income is an INC type, which has a total value of 100, and the total expense is an EXP type, which has a total value of 50, then Revenue will return 150 by aggregating the absolute values of each account, 100 and 50.

If you want to calculate with the existing sign-flipping rules of Account dimensions, then enable the option "CONFIG.FLIPPING_SIGN_ACCORDING_ACCTYPE" beforehand:

Sample Code
CONFIG.FLIPPING_SIGN_ACCORDING_ACCTYPE=ON

DATA([d/Account] ="Revenue")=RESULTLOOKUP([d/Account]="Income") + RESULTLOOKUP([d/Account]="Expense")

In the example above, Revenue will return 50 because the advanced formula flipped the sign of the value 50 from Expense.

For more information about this option, see CONFIG.FLIPPING_SIGN_ACCORDING_ACCTYPE.

Rules of Using Attributes to Filter Certain Dimension Members

In the planning model, a user can define the required attributes of a dimension and leverage the attributes for various purposes.

Scope Definition

With a specific attribute value, a user can filter certain members in the MEMBERSET function. For example, you can write a script like this:

Sample Code
MEMBERSET [d/Entity].[p/Region]="Europe"

This script will filter all the members with the region name "Europe" in the Entity dimension.

Related Members Definitions

  • Data Writing

    If a user needs to define the related members from the same dimension or a different dimension, the attribute can be used to define the scope of dynamic-related members.

    For example, if you have an Entity dimension such as the one below, and you want to build an advanced formula script to calculate the Sales total and Sales Cost total, using the related member calculation.

    ID Description Calculation SisterMember

    Sales Europe

    Sales Europe

    Y

    Sales Total

    Sales Asia

    Sales Asia

    Y

    Sales Total

    Sales Allowance

    Sales Allowance

    Y

    Sales Cost Total

    Sales Rebate

    Sales Rebate

    Y

    Sales Cost Total

    Sales Total

    Sales Total

       

    Sales Cost Total

    Sales Cost Total

       

    Then write the following script:

    Sample Code
    MEMBERSET [d/Entity].[p/Calculation] ="Y"
    
    DATA([d/Entity] =[d/Entity].[p/SisterMember])=RESULTLOOKUP()

    First MEMBERSET returns the four members "Sales Europe", "Sales Asia", "Sales Allowance", and "Sales Rebate" with the attribute calculation ="Y". The next script line is to read all entity members and aggregate their values to the related member "Sales Total" and "Sales Cost Total". Therefore, the values of Sales Europe and Sales Asia are aggregated to Sales Total, and the values of Sales Allowance and Sales Rebate are aggregated to Sales Cost Total.

  • Data Reading

    This kind of dynamic-related member definition can also apply to the data reading.

    Below is an Entity dimension designed to allocate the cost of the global head quarters to each sales lines of business. The global head quarters cost is allocated to the dedicated member "Allocation Source" of each region.

    ID Description Calculation SisterMember

    Allocation Source - Europe

    Allocation Source - Europe

       

    Sales Germany

    Sales Germany

    Y

    Allocation Source - Europe

    Sales UK

    Sales UK

    Y

    Allocation Source - Europe

    Allocation Source - Asia

    Allocation Source - Asia

       

    Sales China

    Sales China

    Y

    Allocation Source - Asia

    Sales Japan

    Sales Japan

    Y

    Allocation Source - Asia

    Then write the following script:

    Sample Code
    MEMBERSET [d/Entity].[p/Calculation] ="Y"
    
    DATA()=RESULTLOOKUP([d/Entity]=[d/Entity].[p/SisterMember])*@AllocationRatio

    First the MEMBERSET function filters out the members of the entity dimension with the attribute calculation ="Y". Then four members ("Sales Germany", "Sales UK", "Sales China", and "Sales Japan") are chosen as the working scope. The next script line reads the allocation source value for each region ("Allocation Source - Europe" and "Allocation Source - Asia") and allocates it based on the allocation ratio (@AllocationRatio) to the four members.