Skip to Content
Technical Articles
Author's profile photo Uladzislau Pralat

Learn SAC Advanced Formula Actions for Common Business Scenarios In No Time

If you want to learn advanced formula actions for common business scenario then this blog is for you. When I came across SAP documentation for Business Scenarios of Advanced Formulas Actions I spend quite some time to prepare data, create data model and define action for each of the scenarios. In this blog I want to share my work with you to save you trouble doing routine work and focus on actual understanding business requirements and advanced formula actions.

Here is a list of scenarios for which I provide implementation information:

Aggregating Dimension Members into Different Groups

Business scenario is described in SAP documentation. Below you find information how setup it in your system.

Model:

Data and Account dimensions:

Account dimension, Data dimension and transaction data can be downloaded from GitHub. Advanced formula action is defined as follow:

DATA([d/Account]=[d/Account].[p/SUBACC])=RESULTLOOKUP()

IF [d/Account].[p/SUBACC] != "" THEN
	DELETE([d/Account]=[d/Account])
ENDIF

Note: I introduced DELETE statement to prevent duplicating data

This is how data looks before running advanced formula action:

and after:

 

Accumulated depreciation planning

Business scenario is described in SAP documentation. Below you find information how setup it in your system.

Model:

Flow and Account dimensions:

Flow dimension, Account dimension and Transactional Data can be downloaded from GitHub. Advanced formula action is defined as follow:

//Set the scope of date dimension from 202003 to 202012
MEMBERSET [d/Date] = "202003" to "202012"
//Get monthly depreciation value, which equals to acquisition cost / duration of a depreciation
DATA ([d/Account]= "12107000", [d/Flow]= "F_INC") =
  RESULTLOOKUP([d/Account]="12103000", [d/Flow]= "F_INC", [d/Date]= "202003") / RESULTLOOKUP([d/Account]="90007000", [d/Flow]= "#", [d/Date]= "202003")


////Loop with the defined date dimension scope, in this case it is from 201803 to 201812
//FOREACH [d/Date]
//  //Calculate the ending balance
//  DATA ([d/Account]= "12107000", [d/Flow]= "F_CLO") =
//    RESULTLOOKUP ([d/Account]= "12107000", [d/Flow]= "F_OPE") + RESULTLOOKUP ([d/Account]= "12107000", [d/Flow]= "F_INC")+ RESULTLOOKUP ([d/Account]="12107000", [d/Flow]="F_DEC")
//  //Carry forward previous closing value to current month's opening value
//  DATA ([d/Account]= "12107000", [d/Flow]= "F_OPE", [d/Date]= NEXT()) = RESULTLOOKUP([d/Account]="12107000", [d/Flow]= "F_CLO" )
//ENDFOR
DATA() = CARRYFORWARD([d/Flow] ,"F_OPE" ,"F_CLO" ,"F_OPE"+ "F_INC"+"F_DEC" )

Note: I improved the script using more performance efficient statement CARRYFORWARD instead of FOREACH. You can play with both. End result is the same in both cases.

This is how data looks before running advanced formula action:

and after:

 

Forecasting HR turnover rates based on historical data

Business scenario is described in SAP documentation. Below you find information how setup it in your system.

Model:

Account dimension:

Account dimension and Transactional Data can be downloaded from GitHub. Advanced formula action is defined as follow.

Step 1:

MEMBERSET [d/Account] = "CLOSING"
MEMBERSET [d/Date] = "202101" to "202112"
DATA([d/Account] = "CLOSING") = RESULTLOOKUP ( [d/Account]="CLOSING", [d/Date] ="202012")

Step 2:

MEMBERSET [d/Account]=("TURNOVERS","CLOSING")
		  
FOREACH [d/Date]
  //Before performing any calculations, copy the closing amount of the prior period to the opening of current period.
  DATA  ([d/Account]= "OPENING") = RESULTLOOKUP ([d/Account]= "CLOSING", [d/Date]= PREVIOUS())
//Forecast turnover number of current period based on the historical turnover rate of the same period last year. current year’s turnover rate = current year’s opening amount * (previous year’s turnovers / previous year’s opening amount)
  DATA  ([d/Account]= "TURNOVERS") = ROUND(RESULTLOOKUP ([d/Account]= "OPENING") * RESULTLOOKUP ([d/Account]= "TURNOVERS", [d/Date]= PREVIOUS (12)) / RESULTLOOKUP ([d/Account]= "OPENING", [d/Date]= PREVIOUS (12)),0)
//Update current period’s closing amount by subtracting turnovers from the opening amount
  DATA  ([d/Account]= "CLOSING") = RESULTLOOKUP ([d/Account]= "OPENING") - RESULTLOOKUP ([d/Account]= "TURNOVERS") 
ENDFOR

This is how data looks before running advanced formula action:

and after:

 

Intercompany elimination on A/R and A/P

Business scenario is described in SAP documentation. Below you find information how setup it in your system.

Model:

Account, Audit, Interco_Entity and Entity dimensions:

Account dimension, Audit dimension, Interco_Entity dimension, Entity dimension and transaction data can be downloaded from GitHub. Advanced formula action is defined as follow:

CONFIG.GENERATE_UNBOOKED_DATA = OFF
CONFIG.FLIPPING_SIGN_ACCORDING_ACCTYPE = ON

MEMBERSET [d/Date] = "202001" TO "202012"
MEMBERSET [d/Audit] = "10"

IF [d/Account] = ("BSA_IC_AR" , "BSA_IC_AP") Then
  DATA ([d/Entity] = ELIMMEMBER ([d/Entity].[h/HIERARCHY], [d/Entity] , [d/Interco_Entity], [d/Entity].[p/ELIMINATION] = "Y" ), [d/Audit] = "30" ) = RESULTLOOKUP () * -1
  DATA ([d/Entity] = ELIMMEMBER ([d/Entity].[h/HIERARCHY], [d/Entity] , [d/Interco_Entity], [d/Entity].[p/ELIMINATION] = "Y" ), [d/Account] = [d/Account].[p/ELIMACC], [d/Audit] = "30" ) = RESULTLOOKUP () 
ENDIF

This is how data looks before running advanced formula action:

and after:

 

 

Assigned Tags

      4 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Thorsten Kalweit
      Thorsten Kalweit

      Thank you very much for your blog. It is very helpful 🙂

       

      I am searching for sample data for the planning module. Are thre any ressources?

      Author's profile photo Uladzislau Pralat
      Uladzislau Pralat
      Blog Post Author

      Hi Thorsten,

      I would recommend to check following resources that come with realistic sample data:

      1. Planning with SAP Analytics Cloud OpenSAP course
      2. SAP Analytics Cloud Planning workshops series (Week 1, Week 2, Week 3 and Week 4)

      Regards, Uladzislau

      Author's profile photo Sebastian Gesiarz
      Sebastian Gesiarz

      Seems like you are taking the SAP's documentation to the next level.

      Great and easy-to-understand examples.

      Thank you, Uladzislau!

      Author's profile photo Zack Ge
      Zack Ge

      Thanks so much for your blog.

       

      I have been looking for sample data to practice the advances formula in data action. This provides me with a way better understanding of calculation scope and logic.

       

      Thank you, Uladzislau.