Technology Blogs by Members
Explore a vibrant mix of technical expertise, industry insights, and tech buzz in member blogs covering SAP products, technology, and events. Get in the mix!
cancel
Showing results for 
Search instead for 
Did you mean: 
uladzislau_pralat
Contributor
11,126
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:


 

 
5 Comments
Labels in this area