Skip to Content
avatar image
Former Member

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

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
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Sep 08, 2017 at 03:49 PM

    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,

    Add comment
    10|10000 characters needed characters exceeded

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

    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
    Add comment
    10|10000 characters needed characters exceeded