Skip to Content
0
Aug 03, 2016 at 12:27 PM

Rolling 12 Custom Measure in SAP BPC 10.1 NW Classic

270 Views

It is not out of the ordinary for a client to want a summation of last 12 months for many accounts. One way of doing this, if the number of accounts is minimal, is to create several dimension formulas, but this could diminish performance. A better way of accomplishing this is to create a Custom Measure called Rolling 12.


I have tried with next code using UJA_MAINTAIN_MEASURE_FORMULA program in se 38:


MEMBER [MEASURES].[YTD] AS 'IIF(([%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="INC" OR [%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="LEQ"),-([MEASURES].[/CPMB/SDATA], CLOSINGPERIOD([%TIME%].[%TIMEBASELEVEL%])),([MEASURES].[/CPMB/SDATA], CLOSINGPERIOD([%TIME%].[%TIMEBASELEVEL%])))' SOLVE_ORDER=3

MEMBER [MEASURES].[PERIODIC] AS 'IIF(([%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="INC" OR [%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="EXP") AND NOT ([%TIME%].CURRENTMEMBER.PROPERTIES("2/CPMB/PERIOD")="TOTAL" OR [%TIME%].CURRENTMEMBER.PROPERTIES("2/CPMB/PERIOD")="Q1" OR [%TIME%].CURRENTMEMBER.PROPERTIES("2/CPMB/PERIOD")="JAN" ), [MEASURES].[YTD]-([MEASURES].[YTD],[%TIME%].LAG(1)), [MEASURES].[YTD])' SOLVE_ORDER=3

MEMBER [MEASURES].[LTM] AS 'IIF([%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="INC" OR [%ACCOUNT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="EXP",SUM(LASTPERIODS(12,CLOSINGPERIOD([%TIME%].[%TIMEBASELEVEL%])),[MEASURES].[PERIODIC]),[MEASURES].[PERIODIC])';SOLVE_ORDER=3

Values get on Excel are not the proper ones. What is more, if I get the PERIODIC and LTD members in the same report , the PERIODIC member gets some wrong values. However, if I get only PERIODIC member in a report, the values retrieved are ok. Any idea? Do you think previous code is ok? Why the new LTM member affect the standard PERIODIC member?