Skip to Content
0

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

Sep 08, 2017 at 03:41 PM

153

avatar image
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
10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

2 Answers

John Leggio
Sep 08, 2017 at 03:49 PM
0

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,

Show 4 Share
10 |10000 characters needed characters left characters exceeded

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

Source have to be scoped instead of target.

0

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?

0

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!

0

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
0
Vadim Kalinin Sep 08, 2017 at 04:11 PM
0

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
Show 1 Share
10 |10000 characters needed characters left characters exceeded
0