Trying to calculate last 3 months average, script logic, SAP BPC

[Former Member]

Version information:

SAP_BW, release:730, SP-Level: 0009; CPMBPC, release:800,SP-Level:0010
Dimensions : members -
REV_ACCOUNT : ZVALUE
REV_CATEGORY: ACTUAL, AVERAGE
REV_CURRENCY : USD
REV_ENTITY : S100, S200, ..... S900
REV_TIME: 2016.001, 2016.002, ..... 2021.012

I'm trying to run this script as a part of DEFAULT.LGF

The ACTUAL Category has been loaded with data (values are stored in ZVALUE) for all the months, while AVERAGE is empty. I would like to calculate the previous 3 months average for each ACTUAL value and save it in the Average category.

For ex: For 2018.005: (AVERAGE) ZVALUE should contain the value = (ACTUAL,2018.004 + ACTUAL,2018.003 + ACTUAL, 2018.002)/3 and this value should be saved in ZVALUE, AVERAGE category.

Here is the code I've come up with so far, this is the first time I'm trying to write a logic script, so please bear with all the numerous mistakes I'm sure are in the script 🙂

Currently it throws the error, REV_TIME is not assigned in Data Manager. If I add the line REV_TIME=%REV_TIME_SET% in the data region of UJKT, this error disappears. Though I'm unsure if this fix is correct.

The new error, which I've been unable to debug so far is: UJK_VALIDATION_EXCEPTION : Invalid TMVL return value.

Been struggling for a long time, please help!

*XDIM_MEMBERSET REV_CURRENCY=USD
*XDIM_MEMBERSET REV_ENTITY=S100,S200,S300,S400,S500,S600,S700,S800,S900
*XDIM_MEMBERSET REV_ACCOUNT=ZVALUE
*XDIM_MEMBERSET REV_CATEGORY=ACTUAL,AVERAGE
*FOR %REV_TIME%=%REV_TIME_SET%
*WHEN REV_CATEGORY
*IS AVERAGE
*REC(EXPRESSION=([REV_TIME].[TMVL(-1,%REV_TIME%)]+[REV_TIME].[TMVL(-2,%REV_TIME%)]+[REV_TIME].[TMVL(-3,%REV_TIME%)])/3, REV_ACCOUNT="ZVALUE",REV_CATEGORY="AVERAGE")
*ENDWHEN
*NEXT

[former_member186338]

In order to calculate average for 3 previous months you have to scope previous months!

The script will be:

*XDIM_MEMBERSET REV_CURRENCY=USD
*XDIM_MEMBERSET REV_ENTITY=S100,S200,S300,S400,S500,S600,S700,S800,S900
*XDIM_MEMBERSET REV_ACCOUNT=ZVALUE
*XDIM_MEMBERSET REV_CATEGORY=ACTUAL //only read member to be scoped!
*FOR %T%=%REV_TIME_SET% //loop months selected
*XDIM_MEMBERSET REV_TIME=TMVL(-1,%T%),TMVL(-2,%T%),TMVL(-3,%T%) //scope prev. months 
*WHEN REV_CATEGORY
*IS * //already scoped to ACTUAL
*REC(EXPRESSION=%VALUE%/3, REV_TIME=%T%,REV_CATEGORY=AVERAGE) // write prev. months to current month
*ENDWHEN
*NEXT

[former_member186338]

P.S. Please read:

https://blogs.sap.com/2014/01/31/how-to-ask-questions-about-script-logic-issues/

https://blogs.sap.com/2012/08/20/testing-logic-script-ujkt/

In UJKT you have to add line:

REV_TIME=2018.005

in the Data Region

[JohnL]

Hi Nikhil,

That's a very old version of BPC, over 4 years old

I suggest you upgrade to the latest version SP24 and re-try your script.

It looks correct to me.

just simplify the script as well:

Try:

*XDIM_MEMBERSET REV_CURRENCY=USD
*XDIM_MEMBERSET REV_ENTITY=S100,S200,S300,S400,S500,S600,S700,S800,S900
*XDIM_MEMBERSET REV_ACCOUNT=ZVALUE
*XDIM_MEMBERSET REV_CATEGORY=ACTUAL,AVERAGE
*FOR %REV_TIME%=2017.02
*WHEN REV_CATEGORY
*IS AVERAGE
*REC(EXPRESSION=([REV_TIME].[TMVL(-1,%REV_TIME%)]), REV_ACCOUNT="ZVALUE",REV_CATEGORY="AVERAGE")
*ENDWHEN
*NEXT

Thanks,

[former_member186338]

This script will do nothing - no record in 2017.02 - no WHEN/ENDWHEN loop!

Source have to be scoped instead of target.

[JohnL]

Hi Vadim,

If you don't scope the time then it will scope all the available time members.
Maybe that's what he wants. Maybe not 🙂

Actually it might be better to try to create a custom measure to perform this calculation? Thoughts?

[former_member186338]

Please look on the code in my answer:

For each TIME period (%T%) in %REV_TIME_SE% 3 previous months are scoped using:

*XDIM_MEMBERSET REV_TIME=TMVL(-1,%T%),TMVL(-2,%T%),TMVL(-3,%T%)

And result is written to %T%

*REC(EXPRESSION=%VALUE%/3, REV_TIME=%T%,REV_CATEGORY=AVERAGE)

Custom measure can be an option in some cases, but can also affect performance. But it depends on requirements!

[former_member186338]

P.S. Look on UJKT results for:

Data Region:

TIME=2017.09,2017.10,2017.11

Script:

*FOR %T%=%TIME_SET%
*XDIM_MEMBERSET TIME=TMVL(-1,%T%),TMVL(-2,%T%),TMVL(-3,%T%)
*NEXT

Log for Execute(Simulate):

LGX:

*XDIM_MEMBERSET TIME= 2017.08 , 2017.07 , 2017.06
*XDIM_MEMBERSET TIME= 2017.09 , 2017.08 , 2017.07
*XDIM_MEMBERSET TIME= 2017.10 , 2017.09 , 2017.08