Question Re: Rolling 12 Custom Measure in SAP BPC 10.1 NW Classic in Financial Management Q&A

Rolling 12 Custom Measure in SAP BPC 10.1 NW Classic

fernando_santosgonzalez2 — Wed, 03 Aug 2016 12:27:53 GMT

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?

Re: Rolling 12 Custom Measure in SAP BPC 10.1 NW Classic

former_member186338 — Wed, 03 Aug 2016 12:53:09 GMT

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

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

Re: Rolling 12 Custom Measure in SAP BPC 10.1 NW Classic

fernando_santosgonzalez2 — Wed, 03 Aug 2016 15:00:19 GMT

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

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.

Re: Rolling 12 Custom Measure in SAP BPC 10.1 NW Classic

former_member186338 — Wed, 03 Aug 2016 15:42:38 GMT

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

Re: Rolling 12 Custom Measure in SAP BPC 10.1 NW Classic

former_member186338 — Wed, 03 Aug 2016 15:47:24 GMT

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

Re: Rolling 12 Custom Measure in SAP BPC 10.1 NW Classic

fernando_santosgonzalez2 — Wed, 03 Aug 2016 16:17:56 GMT

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.

Re: Rolling 12 Custom Measure in SAP BPC 10.1 NW Classic

former_member186338 — Wed, 03 Aug 2016 16:50:37 GMT

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

Re: Rolling 12 Custom Measure in SAP BPC 10.1 NW Classic

fernando_santosgonzalez2 — Wed, 03 Aug 2016 17:24:16 GMT

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.

Re: Rolling 12 Custom Measure in SAP BPC 10.1 NW Classic

former_member186338 — Wed, 03 Aug 2016 17:34:36 GMT

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.

Re: Rolling 12 Custom Measure in SAP BPC 10.1 NW Classic

JohnL — Wed, 03 Aug 2016 18:15:12 GMT

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