SAP Analytics Cloud - Data Action example

Business Scenario: Calculate the accumulated monthly values in a new measure

Though model calculations were defined using the ITERATE function to calculate the accumulative sum of monthly values, the on-the-fly calculations were generating incorrect values in the story when a filter was applied at monthly level (Ex. a story filter on Dec 2022 would show Total hours as 81.24 instead of 556.69) 

Components in a Data Action explained:

Scope:

MEMBERSET command is used to define the scope of the Data Action

Ex. MEMBERSET [d/MEASURES] = ("CONTRACT_HRS_CONSUMED")

In the example, the scope of the calculation is restricted to the measure CONTRACT_HRS_CONSUMED

Lookup:

RESULTLOOKUP command returns the values based on the parameters 

Ex. RESULTLOOKUP([d/TIME] = PREVIOUS(1, "MONTH", [d/TIME]))

Here, the RESULTLOOKUP command fetches the previous month values   

Writing results:

DATA command is used to write data into the SAC model

Ex. DATA([d/MEASURES] = "CONTRACT_HRS_PRIOR") = RESULTLOOKUP([d/TIME] = PREVIOUS(1, "MONTH", [d/TIME]))

Here, the previous month values are written to the Measure - CONTRACT_HRS_PRIOR  

Use of Variables:

Variables are used to store the values temporarily during calculations

Ex. VARIABLEMEMBER #SUM OF [d/MEASURES]

Here the variable is defined with the name #SUM

Ex. DATA([d/MEASURES] = #SUM) = RESULTLOOKUP([d/MEASURES] = "CONTRACT_HRS_USED")

The value of the measure CONTRACT_HRS_USED is temporarily stored in the Variable #SUM

Use of Loops:

For Next Loop:

In this scenario we have used both For Next and IF THEN loops to achieve the desired results. 

Ex. FOREACH [d/TIME]

.

.

ENDFOR

Ex. IF DATEDIFF([d/it_PROJECT_DEF].[p/PROJECT_CLOSEDATE], [d/TIME], "MONTH") <= 0 THEN

DATA([d/MEASURES] = "CONTRACT_HRS_USED") = ...

ENDIF

RESULT:

The story displays correct cumulative monthly values even when filtered on individual month, as values are written to the SAC model using Data Action.

Code:

MEMBERSET [d/MEASURES] = ("CONTRACT_HRS_CONSUMED")

MEMBERSET [d/TIME] = PREVIOUS(36, "MONTH", TODAY()) TO PERIOD(TODAY())

VARIABLEMEMBER #SUM OF [d/MEASURES]

DATA([d/MEASURES] = "CONTRACT_HRS_PRIOR") = RESULTLOOKUP([d/TIME] = PREVIOUS(1, "MONTH", [d/TIME]))

DATA([d/MEASURES] = #SUM) = RESULTLOOKUP([d/MEASURES] = "CONTRACT_HRS_PRIOR")

FOREACH [d/TIME]

IF DATEDIFF([d/it_PROJECT_DEF].[p/PROJECT_CLOSEDATE], [d/TIME], "MONTH") <= 0 THEN

DATA([d/MEASURES] = "CONTRACT_HRS_USED") = RESULTLOOKUP([d/TIME] = PREVIOUS(1, "MONTH", [d/TIME]), [d/MEASURES] = #SUM) + RESULTLOOKUP()

DATA([d/MEASURES] = #SUM) = RESULTLOOKUP([d/MEASURES] = "CONTRACT_HRS_USED")

ENDIF

ENDFOR