How to derive previous twelve months Total in BPC Script Logic.

[former_member740905]

Hello,

I have a requirement where cost need to be calculated based on below formula.

Ex: Product Cost calculation in march 2021 forecast.

Product Cost = Product Cost Actuals(Jan and Feb 2021)+(Mar to Dec 2020)/ Product Revenue Actuals((Jan and Feb 2021)+(Mar to Dec 2020))* Forecast Product Revenue.

Whereas forecast Product Revenue input by the user.

How to derive Previous twelve month Total of Actuals for Product cost and Revenue in script Logic?

[Former Member]

Thank you for visiting SAP Community to get answers to your questions. Since you're asking a question here for the first time, I recommend you to read this overview:

https://community.sap.com/resources/questions-and-answers and to take our Q&A tutorial at: https://developers.sap.com/tutorials/community-qa.html.

With these tips you'll be able to prepare questions that draw responses from our members. The more details you provide, the more likely it is that members will be able to answer your question. Should you wish, you can revise your question by selecting Actions, then Edit. By adding a picture to your profile you encourage readers to respond: https://www.youtube.com/watch?v=46bt1juWUUM

Best,

Your SAP Community moderator

[former_member186338]

You need to get list of 12 TIME members based on current month selected by user in %TIME_SET%

The logic will be:

*SELECT(%Y%,YEAR,TIME,ID=%TIME_SET%)
*SELECT(%TID%,TIMEID,TIME,ID=%TIME_SET%)
*SELECT(%MN%,MONTHNUM,TIME,ID=%TIME_SET%)
*SELECT(%TIDJAN%,[TIMEID],TIME,YEAR=%Y% AND MONTHNUM=1 AND CALC=N)
*SELECT(%TIDL%,[TIMEID],TIME,TIMEID<%TIDJAN% AND CALC=N)
*SELECT(%PREVYEAR%,[YEAR],TIME,TIMEID<%TIDJAN% AND TIMEID>=%TIDL% AND CALC=N)
*SELECT(%PREVTID%,TIMEID,TIME,MONTHNUM=%MN% AND YEAR=%PREVYEAR% AND CALC=N)
*SELECT(%PREV12%,ID,TIME,TIMEID>=%PREVTID% AND TIMEID<%TID% AND CALC=N)

*XDIM_MEMBERSET TIME=%PREV12%

For example, if %TIME_SET%=2021.04 then result is:

*XDIM_MEMBERSET TIME=2020.04,2020.05,2020.06,2020.07,2020.08,2020.09,2020.10,2020.11,2020.12,2021.01,2021.02,2021.03

[former_member740905]

Hi Vadim,

Thanks for update. Based on your logic, we can derive last 12 months.

As per current logic, cost account is being calculated month wise. But as per our scenario it should calculate based on Total amount not month wise.

For ex:

Should be scenario:

Forecasted Cost ABC = (Actual Cost ABC(Nov2020+Dec2020+Jan2021+feb2021)/Actual Revenue ABC(Nov2020+Dec2020+Jan2021+feb2021))*Forecasted Revenue ABC

Actual Cost ABC

Nov 2020- 30, Dec2020- 40, Jan 2021- 50 and Feb 2021- 60

Actual Revenue ABC

Nov 2020- 60, Dec2020- 100, Jan 2021- 120 and Feb 2021- 110

Forecasted Revenue ABC is 50

Forecasted Cost ABC = (30+40+50+60/60+100+120+110)*50

Forecasted Cost ABC = (180/390)*50 = 23.07

Forecasted Cost ABC should be 23.07

But as per current Logic cost is being calculated month wise.

Forecasted Cost ABC= ((30/60)*50(Nov 2020)+(40/100)*50(Dec 2020)+(50/120)*50(Jan 2021)+60/110*50(Feb 2021))

Forecasted Cost ABC= (25+20+41.67+27.27) = 113.94

Forecasted Cost ABC is 113.94 as per current logic which is wrong, it should be 23.07 which has been calculated in above formula.

How shall we derive formula based on Total value?

[former_member186338]

mkhemnani

"But as per our scenario it should calculate based on Total amount not month wise." - Sorry, but what is the issue?

Use some temporary account to aggregate values for previous months in the current month! Then use calculated value in your logic.

*XDIM_MEMBERSET TIME=%PREV12%
*WHEN ACCOUNT 
*IS ACT_COST
*REC(EXPRESSION=%VALUE%,ACCOUNT=ACT_COST_12AGGR,TIME=%TIME_SET%)
*ENDWHEN...

Actually, you need to aggregate only Revenue - denominator