Skip to Content
avatar image
Former Member

Understanding MDX Formulas

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.

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Jan 08 at 01:28 PM

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

    Add comment
    10|10000 characters needed characters exceeded