SAP BPC 10.1 NW - MDX Formula Question

[SorroAndres]

Hello everyone,

I'm facing a particular requierement with a client, where he needs to query a member by category. If the member is querying for an ACTUAL category, it should retrieve the data for the month in the corresponding axe (ej. Jan-19 in the axe, it retrieves data for Jan-18). If the category is planning, just retrieve the data. So I made a MDX formula member like this:

IIF([CATEGORY].CurrentMember.Properties("TYPE")="ACTUAL",([INDICATOR].[KI01],[TIME].CURRENTMEMBER.LAG(12)),[INDICATOR].[KI01])

It works, but only at MONTH level. Is there any way to make it work for QUARTER and YEAR level? I know I can achieve this through other methods, but would be nice to know if it's possible via MDX.

Thanks in advance.

Andres

[former_member186338]

I have already provided you a fully working formula! Please read my previous answer

https://answers.sap.com/answers/662066/view.html

[former_member186338 · Accepted solution]

You can test for LEVEL property like:

IIF([TIME].CURRENTMEMBER.PROPERTIES("LEVEL")="MONTH",([PL010],[TIME].CURRENTMEMBER.LAG(12)),IIF([TIME].CURRENTMEMBER.PROPERTIES("LEVEL")="QUARTER",([PL010],[TIME].CURRENTMEMBER.LAG(4)),IIF([TIME].CURRENTMEMBER.PROPERTIES("LEVEL")="YEAR",([PL010],[TIME].CURRENTMEMBER.LAG(1)),NULL)))

[PL010] = [INDICATOR].[KI01] in my test case

P.S. Just tested, working fine in my test!

PL250 - member with member formula.

P.P.S.

Full formula

IIF([CATEGORY].CURRENTMEMBER=[CATEGORY].[Actual],IIF([TIME].CURRENTMEMBER.PROPERTIES("LEVEL")="MONTH",([PL010],[TIME].CURRENTMEMBER.LAG(12)),IIF([TIME].CURRENTMEMBER.PROPERTIES("LEVEL")="QUARTER",([PL010],[TIME].CURRENTMEMBER.LAG(4)),IIF([TIME].CURRENTMEMBER.PROPERTIES("LEVEL")="YEAR",([PL010],[TIME].CURRENTMEMBER.LAG(1)),NULL))),[PL010])

[SorroAndres]

Vadim,

I've tried the LEVEL approach, works like a charm!

Thanks!

[SorroAndres]

Hi Vadim,

Thanks for your response. In the CATEGORY dimension they had previously a property named "TYPE", to know what kind of category they were using (plan, actual, forecast, etc). I just used the property to be able to call the ACTUAL ID (same name for both ID and Property).

As I mentioned, the formula is working for months. I'll try to make it work with ParallelPeriod for calc member flag=Y or by each level, and I'll let you know

Thanks again!

[former_member186338]

What do you mean by:

[CATEGORY].CurrentMember.Properties("TYPE")="ACTUAL"

In the description of your question I can see "If the member is querying for an ACTUAL category"... ID or Property?

Then, for sure your formula is not working due to LAG(12)

You can use multiple IF to determine LAG value or try to play with ParallelPeriod