SAP Analytics Cloud Planning : examples of configuration of revenue driver calculations without using If statements in advanced formulas

[Charlotte]

During a partner workshop, when presenting SAP Analytics Cloud best practices (please refer to one of my previous blogs SAP Analytics Cloud Planning : do you encounter performances issues? What can you do before contacti...)

I was challenged by our partners about a use case requiring lots of If statements in advanced formulas.

This blog presents a commonly configured use case with If statements that I suggest replacing with a lighter, easier to maintain configuration.

I would like to take the opportunity of this blog to thank my colleague Christian Schoenbein who, tirelessly, help me elaborating and testing my experiences of configuration, off the beaten track 🙂

Use case

Some of the revenues in Financial Planning aren't input but calculated, based on other existing data.

For example,

• Revenue A = driver A1 x driver A2


• Revenue B = driver B1 x driver B2

My proposal is to define in the model the constants of the calculation for every revenue with two properties, one per member of the calculation.

In my illustration, I tried to use some significant labels for every multiplier.

Option 1 (model with Account dimension)

This model has

• an Account dimension


• a generic dimension storing the Metrics required for the calculation of the data of the Revenues,


• 3 measures: “MetricX” and “MetricY” , corresponding to the 2 drivers, once multiplied, equals to the third measure “Amount”.

This configuration involves an advanced formula that fetches the data stored in the Metrics, with reference to the properties defined in the Account dimension, to allocate the amounts.

In this type of configuration, you don’t have to create If Statements fetching the amounts depending on the Account, you want to calculate, everything is dynamically maintained in the model.

This setting requires that the labels of members of the generic dimension and of the properties of the account dimension are identical.

Please find below some screenshots of the tested configuration.

Account dimension setting

Both properties refer to the details of the calculations with significant labels

In this use case, for example, the "Revenue B2B" equals the "customer average basket items" multiplied by the "items"

 

Generic dimension setting

This dimension stores the data of the metrics, used in the Account calculation.

 

Script of the Advanced formula

MEMBERSET [d/Measures] = ("MetricX", "MetricY")

MEMBERSET [d/Account] = ("Revenue B2B", "Revenue B2C")

MEMBERSET [d/Metrics]="#"

MEMBERSET [d/Organization]=%Organization%

MEMBERSET [d/Date]=BASEMEMBER([d/Date],%Date%)

 

Fetching the data of the 2 drivers in using the properties of the Account dimension (in red below)

 DATA([d/Measures]="MetricX")=RESULTLOOKUP([d/Measures]="Amount",[d/Account]="Metric ",[d/Metrics]=[d/Account].[p/MetricX])

DATA([d/Measures]="MetricY")=RESULTLOOKUP([d/Measures]="Amount",[d/Account]="Metric ",[d/Metrics]=[d/Account].[p/MetricY])

Calculation of the Amount

DATA([d/Measures]="Amount")=RESULTLOOKUP([d/Measures]="MetricX")*RESULTLOOKUP([d/Measures]="MetricY")

 

Result 

 

 

Option 2 (measure based model without any dimension Account)

Here the same logic applies as in option 1 but without an Account dimension but measures and one Generic dimension that stores everything: dimension members, the input, calculations and the properties to assign the members required for the calculation.

Measures 

 

Generic dimension

 

Script of the Advanced formula

MEMBERSET [d/Driver] = ("Revenue_B2B","Revenue_B2C")

 

// fill scope from parameters

MEMBERSET [d/Organization] = %TargetOrg%

MEMBERSET [d/Date] = BASEMEMBER([d/Date] , %TargetDate%)

 

// run copy and calculation

DATA([d/Measures]="Metric_X") = RESULTLOOKUP([d/Measures]="Input", [d/Driver]=[d/Driver].[p/Metric_X])

DATA([d/Measures]="Metric_Y") = RESULTLOOKUP([d/Measures]="Input", [d/Driver]=[d/Driver].[p/Metric_Y])

DATA([d/Measures]="Revenue") = RESULTLOOKUP([d/Measures]="Metric_X") * RESULTLOOKUP([d/Measures]="Metric_Y")

 

Result

 

Hope this helps 🙂

Charlotte