Monday, July 28, 2014

LAG an LEAD

The LAG and LEAD functionality allow to jump back or forward a number of step on the same hierarchy level.
LAG(1) and LEAD(1) is the same as PrevMember and Next Member.
WITH
MEMBER [Measures].[Previous Sales Amount -1] AS
    SUM([Measures].[Sales Amount],[Date].[Calendar].CURRENTMEMBER.LAG(1)), FORMAT_STRING="Currency"
MEMBER [Measures].[Previous Sales Amount -2] AS
    SUM([Measures].[Sales Amount],[Date].[Calendar].CURRENTMEMBER.LAG(2)), FORMAT_STRING="Currency"
MEMBER [Measures].[Previous Sales Amount -4] AS
    SUM([Measures].[Sales Amount],[Date].[Calendar].CURRENTMEMBER.LAG(4)), FORMAT_STRING="Currency"
MEMBER [Measures].[Next Sales Amount +1] AS
    SUM([Measures].[Sales Amount],[Date].[Calendar].CURRENTMEMBER.LEAD(1)), FORMAT_STRING="Currency"
MEMBER [Measures].[Next Sales Amount +2] AS
    SUM([Measures].[Sales Amount],[Date].[Calendar].CURRENTMEMBER.LEAD(2)), FORMAT_STRING="Currency"
MEMBER [Measures].[Next Sales Amount +4] AS
    SUM([Measures].[Sales Amount],[Date].[Calendar].CURRENTMEMBER.LEAD(4)), FORMAT_STRING="Currency"
    
SELECT
    {
          [Measures].[Sales Amount]
        , [Measures].[Previous Sales Amount -1]
        , [Measures].[Previous Sales Amount -2]
        , [Measures].[Previous Sales Amount -4]       
        , [Measures].[Next Sales Amount +1]
        , [Measures].[Next Sales Amount +2]
        , [Measures].[Next Sales Amount +4]        
    } ON COLUMNS,
[Date].[Calendar Year].MEMBERS ON ROWS
FROM [Adventure Works]
Will give use:
image
Till Next Time And Have Fun.

No comments:

Post a Comment