Rolling 12 Custom Measure in SAP BPC 10.1 NW Classic

[fernando_santosgonzalez2]

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?

[former_member186338]

Your cube is YTD? Please post standard periodic measure to compare.

P.S. Alway provide screenshots of sample reports to explain the issue!

[fernando_santosgonzalez2]

Yes, the cube is YTD.

Standard PERIODIC Measure member is detailed next:

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

Attached 2 screenshots:

1.- A report with members of the dimension MEASURES: YTD,PERIODIC and LTM, at the the bottom of the screenshot (wrong data is orange-coloured). In the upper part of the screenshot, data manually calculated. 

2.- Same report taking LTM member out, at the the bottom of the screenshot (all data is ok).

Thank you for your time.

[former_member186338]

I suspect SP issue. Please provide detailed info about bpc and BW version and sp.

[former_member186338]

P.S. You may test - just create a new custom measure and copy the text of periodic!

[fernando_santosgonzalez2]

I have tried to add a new measure with same formular as periodic member and it works, we get same values as periodic.

Attached screetshot of diferent versions of BPC, BW and EPM.

[former_member186338]

I see the question is marked answered. No more issues?

[fernando_santosgonzalez2]

It is not answered. I haven't changed the status of the post. I don't know how to change the post to unresolved again.

[former_member186338]

Sorry, but the post can't be marked as assumed answered by itself   Only you and moderator can do it, but I am 100% sure that moderator will never do it in this case.

About solution - both your BPC and BW SP's are old. If you upgrade to the recent the issue will be solved.

[JohnL]

Hi Fernando, See if this post helps: http://scn.sap.com/thread/3415212 Thanks,