Level 02,Lag in custom measures

[manohar_pappireddy]

Hi,

We are on BPCNW10.SP level is 10.

We have a custom measure.Formula for this is:

IIF([%TIME%].CURRENTMEMBER.PROPERTIES("2/CPMB/CNPW963")="365",

((([MEASURES].[/CPMB/SDATA],CLOSINGPERIOD([%TIME%].[LEVEL02]).LAG(11))*30 + ([MEASURES].[/CPMB/SDATA],CLOSINGPERIOD([%TIME%].[LEVEL02]).LAG(10))*31 + ([MEASURES].[/CPMB/SDATA],CLOSINGPERIOD([%TIME%].[LEVEL02]).LAG(9))*31 + ([MEASURES].[/CPMB/SDATA],CLOSINGPERIOD([%TIME%].[LEVEL02]).LAG(8))*28 + ([MEASURES].[/CPMB/SDATA],CLOSINGPERIOD([%TIME%].[LEVEL02]).LAG(7))*31 + ([MEASURES].[/CPMB/SDATA],CLOSINGPERIOD([%TIME%].[LEVEL02]).LAG(6))*30 + ([MEASURES].[/CPMB/SDATA],CLOSINGPERIOD([%TIME%].[LEVEL02]).LAG(5))*31 + ([MEASURES].[/CPMB/SDATA],CLOSINGPERIOD([%TIME%].[LEVEL02]).LAG(4))*30 + ([MEASURES].[/CPMB/SDATA],CLOSINGPERIOD([%TIME%].[LEVEL02]).LAG(3))*31 + ([MEASURES].[/CPMB/SDATA],CLOSINGPERIOD([%TIME%].[LEVEL02]).LAG(2))*31 + ([MEASURES].[/CPMB/SDATA],CLOSINGPERIOD([%TIME%].[LEVEL02]).LAG(1))*30 + ([MEASURES].[/CPMB/SDATA],CLOSINGPERIOD([%TIME%].[LEVEL02]).LAG(0))*31)/365),

Any body explains me what is LEVEL02 and LAG.

NB:CPMB/CNPW963 is days in a year property for TIME dimension.

Thanks,

Manohar

[Former Member · Accepted solution]

Hi Manohar,

Here LEVEL is the node level in the hierarchy.

In your case for Time, Year will be LEVEL00, Quarter will be LEVEL01, Month will be LEVEL02

And LAG is the Previous member node within same node level.

e.g., if you consider month in hand is December then - LAG(0) will be December, LAG(1) will be November and so on...

Thanks

Regards,

Prashant

[former_member186338]

Hi Manohar,

Please read Microsoft site about MDX statements: Lag (MDX)

Vadim