Friends,
If you have read the article about LAG then you will easily say that LEAD is just opposite to LAG function after finished reading this article.LEAD 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.Lead(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 forward by one Member, if 2(Two) then will navigate forward by two members from the current member.
Positive Lead and Negative Lead –
If you specify a POSITIVE number as Index then it will go forward from the current member and this is called POSITIVE LEAD. Lets see an example for Positive Lead here.
Select [Date].[Fiscal].[Month].[January 2001].Lead(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 LEAD(5) and hence it will navigate forward by FIVE members and will return “June 2001” as output if that member exists in Cube.
If you specify a NEGATIVE number as Index then it will go back from the current member and this is called NEGATIVE LEAD. Lets see an example for Negative Lead here.
Select [Date].[Fiscal].[Month].[January 2001].LEAD(-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 LEAD(-5) and hence it will navigate back by FIVE members and will return “August 2000” as output if that member exists in Cube.
PFB screenshot which shows an example for Lead function.
Note – Lead 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
