LAG Function in MDX

Friends,

If you have read the article about LEAD then you wil easily say that LAG is just opposite to LEAD function after finished reading this article. LAG is a Navigation Function which returns a value(member) that is N number of positions following the supplied member in the given attribute. Typical uses of this function is to navigate back or forward to N positions from the given member. PFB the syntax of LEAD function –

Member_Expression.Lag(Index)

Member_Expression - A valid Attribute Member .

Index - The number of positions we wish to navigate.

Member positions within a level are determined by the attribute hierarchy’s natural order. The numbering of the positions is zero-based. If the specified lead is zero (0), the Lead function returns the specified member. If you specified 1(One) then it will take you backward by one Member, if 2(Two) then will navigate backward by two members from the current member.

Positive Lag and Negative Lag –

If you specify a POSITIVE number as Index then it will go backward from the current member and this is called POSITIVE LAG. Lets see an example for Positive Lag here.

Select [Date].[Fiscal].[Month].[January 2001].Lag(5) on 0

from [Adventure Works].

If you see the above query member expression is “[Date].[Fiscal].[Month].[January 2001]” and current member is “January 2001”. We have given Lag(5) and hence it will navigate back by FIVE members and will return “August 2000” as output if that member exists in Cube.

If you specify a NEGATIVE number as Index then it will go forward from the current member and this is called NEGATIVE LAG. Lets see an example for Negative Lag here.

Select [Date].[Fiscal].[Month].[January 2001].LAG(-5) on 0

from [Adventure Works].

If you see the above query member expression is “[Date].[Fiscal].[Month].[January 2001]” and current member is “January 2001”. We have given Lag(-5) and hence it will navigate forward by FIVE members and will return “June 2001” as output if that member exists in Cube.

PFB screenshot which shows an example for Lag function.

Note – Lag looks like a simple function and not much useful one in real time scenarios but this function in combination with other functions like CLOSING PERIOD or RANGE functions will be most powerful and useful one.

Remember – Positive Lead = Negative Lag and Negative Lead = Positive Lag

That’s it  .. Happy Coding !!

Regards,

Roopesh Babu V