Understanding MDX Formulas

[Former Member]

Hi,

I am trying to understand a custom formula that SAP made for my company in which a new MEASURE is created for its fiscal year.

IIF( [%GRPACCT%].CURRENTMEMBER.PROPERTIES("2/CPMB/ACCTYPE")="INC",
IIF( ([%TIME%].CURRENTMEMBER.PROPERTIES("2/CPMB/FQPQDSA")="X"), 
-([MEASURES].[/CPMB/SDATA], CLOSINGPERIOD([%TIME%].[LEVEL02])),
IIF( ([%TIME%].CURRENTMEMBER.PROPERTIES("2/CPMB/SADA")="Q2" OR [%TIME%].CURRENTMEMBER.PROPERTIES("2/CPMB/PERIOD")="Q3" OR [%TIME%].CURRENTMEMBER.PROPERTIES("2/CPMB/PERIOD")="Q4" ),  


-([MEASURES].[/CPMB/SDATA], CLOSINGPERIOD([%TIME%].[LEVEL02])) 
+([MEASURES].[/CPMB/SDATA], CLOSINGPERIOD([%TIME%].[LEVEL02]).LAG(3)), 
-([MEASURES].[/CPMB/SDATA], CLOSINGPERIOD([%TIME%].[LEVEL02]))
+([MEASURES].[/CPMB/SDATA], CLOSINGPERIOD([%TIME%].[LEVEL02]).LAG(1))
                                                           )
),

The second IIF is to check if it is the beggining of period (Q1).

I need to understand the last 4 lines of the IIF and the signs before MEASURES:

-([MEASURES]

+([MEASURES]

Is it making a substraction or addition?.

I understand that CLOSINGPERIOD([%TIME%].[LEVEL02]) is selecting the last sibling of the current Quarter.

Thank you very much in advance.

[former_member186338]

May be it's better to explain, what do you want to achieve with custom measure?

[Former Member]

Hi Vadim,

The code is working properly, that's just a piece of the code that I want to understand.

It is making a logic in order to create a measure for the fiscal year period which goes from September to August.

Thank you.

[former_member186338]

It’s a bad idea in general to create a custom measure for non standard fiscal year! Strange proposal from consultants (even if you are talking about SAP consultants). Time dimension properties have to be correctly configured and the standard measure will work correctly!

[Former Member]

I appreciate the advice Vadim, I will remember it for the future and study the dimension properties you are talking about. But the request that I have is to explain this code (that they already have set up and working) to the client, I need to understand what are these lines doing in the formula.

Thank you very much.

[former_member186338]

Sorry, but it's a waste of time to explain the incorrect approach (with measure formula)!

[former_member186338]

P.S. If you explain the reporting requirements I can provide you a correct solution.