BPC logic script to subtract current month data with previous month

[former_member590808]

Hello

We have a YTD BPC model and we have to fetch period values in it. Example 2018.06 = 2018.06 (YTD Data)-2018.05(YTD Data), 2018.08 = 2018.08 (YTD Data)-2018.07(YTD Data) . I have written below logic script but it is giving me error" No members found in dimension "APL_TIMES" for condition "CALC" = "N" on validation. I am trying to fetch Actuals data stored at ACT and save the derived value in ACT_M

*XDIM_MEMBERSET APL_CATEGORY = ACT,ACT_M
*XDIM_MEMBERSET APL_KEYFIGURE = AMT, Dummy_test
*XDIM_MEMBERSET APL_TIMES = %APL_TIMES_SET%
*XDIM_MEMBERSET APL_AUDITTRAIL = 333,328,344
*XDIM_MEMBERSET APL_ENTITY = BAS(APIL)
*XDIM_MEMBERSET APL_CURRENCY = LC
*XDIM_MEMBERSET APL_PRODUCT = NOPRODUCT
*XDIM_MEMBERSET APL_PC = PC_NONE
*XDIM_MEMBERSET APL_PLANYEAR = NOPLANYEAR
*XDIM_MEMBERSET APL_CC = NOCC
*XDIM_MEMBERSET APL_ACCOUNTS = H01001,L01001


*WHEN APL_ENTITY
	*IS BAS(APIL)
	*WHEN APL_KEYFIGURE
		*IS AMT
		*WHEN APL_CATEGORY
			*IS ACT
				*REC(EXPRESSION=%VALUE%+[APL_TIMES].[TMVL(-1,%APL_TIMES_SET%)],APL_CATEGORY = ACT_M, APL_KEYFIGURE = Dummy_test)
				//*REC(EXPRESSION = %VALUE%+([APL_TIMES].[2018.05]),APL_CATEGORY = ACT_M, APL_KEYFIGURE = Dummy_test)
			*ENDWHEN
		*ENDWHEN
	*ENDWHEN

[former_member186338 · Accepted solution]

Can be written using PREVMEM

The PERIODIC value for each selected month is calculated by formula:

PERIODIC selected month = IF MONTHNUM=1 THEN YTD selected month ELSE

YTD selected month - YTD PREVMEM month

*LOOKUP SameModel
*DIM PREVMONTH:TIME=TIME.PREVMEM
*DIM MEASURES="YTD"
*ENDLOOKUP 

*XDIM_MEMBERSET ACCOUNT=PL110
*XDIM_MEMBERSET TIME=BAS(2007.TOTAL)
*XDIM_MEMBERSET MEASURES = YTD

*WHEN TIME.MONTHNUM
*IS 1
*REC(EXPRESSION = %VALUE%,ACCOUNT=PL120)
*ELSE
*REC(EXPRESSION = %VALUE%,ACCOUNT=PL120)
*REC(EXPRESSION = -LOOKUP(PREVMONTH),ACCOUNT=PL120)
*ENDWHEN

Sorry, corrected the code!

[former_member186338 · Accepted solution]

nehayadav0911

Sorry again, corrected wrong code. LOOKUP added

[former_member590808 · Accepted solution]

Thanks Vadim for quick revert!

[former_member590808 · Accepted solution]

Hello Vadim,

If I have to achieve this YTD -> Periodic conversion of data but I don't have PREVMEM property in my Times dimension . Is there a way to achieve it

? Attaching the screenshot for reference.

[former_member186338 · Accepted solution]

But what is the issue??

Just create a new custom property with the size 32 char - PREVMEM

And fill it with corresponding values.

[former_member186338]

You can also try the following code (not using property):

//%TIME_SET% - some list of time members like 2021.02,2021.05
*XDIM_MEMBERSET ACCOUNT=PL110
*XDIM_MEMBERSET MEASURES = YTD

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

*WHEN TIME.MONTHNUM
*IS 1
  *REC(EXPRESSION = %VALUE%,ACCOUNT=PL120)
*ELSE
  *WHEN TIME
  *IS %T%
    *REC(EXPRESSION = %VALUE%,ACCOUNT=PL120)
  *IS TMVL(-1,%T%)
    *REC(EXPRESSION = -%VALUE%,TIME=%T%,ACCOUNT=PL120)
  *ENDWHEN
*ENDWHEN

*NEXT

It will work for any list of time members, but is can be slower

[former_member186338]

Unable to understand your logic completely. But looks like you want to perform YTD -> PERIODIC conversion in the same model.

Please read my blog (second part):

https://blogs.sap.com/2018/11/02/periodic-ytd-conversion-using-script-logic-in-bpc-nw/

[former_member590808]

Hello

Thanks for revert. I don't have any Next Member propetry maintained in APL_TIMES. Rather I have Previous Member property maintained against Time . Attaching Screenshot for reference: