mdx tail function not working on currentmember

  • Hi All mdx gurus,

    I was checking out the tail function to achieve same the rolling 3 month sales which works using the lag function...as follows :

    -- Previous 3 month sales

    WITH MEMBER [Measures].[PrevThreeMonthSales] AS 'SUM( [Order Date].[CalendarHierarchy].CurrentMember : [Order Date].[CalendarHierarchy].CurrentMember.Lag(2), [Measures].[Sales Amount])'

    SELECT {[Measures].[Sales Amount], PrevThreeMonthSales} ON 0,

    [Order Date].[CalendarHierarchy].[Month] On 1

    FROM MyFirstCube

    this does not however using tail substitution:

    -- Previous 3 month sales using tail

    WITH MEMBER [Measures].[PrevThreeMonthSales] AS 'SUM(Tail([Order Date].[CalendarHierarchy].CurrentMember,3), [Measures].[Sales Amount])'

    SELECT {[Measures].[Sales Amount], PrevThreeMonthSales} ON 0,

    [Order Date].[CalendarHierarchy].[Month] On 1

    FROM MyFirstCube

    all i get is the same value for the current member

    Question ? Can the tail function not create dynamic sets on currentmember

  • You need to provide the Tail function with a set - you have provided the current member which is just one value ie a set with one value

    So Tail(CurrentMember,3) = CurrentMember.

    An example I use in my cubes (to get the latest year with revenue)

    TAIL(NONEMPTY({[Date].[Year].Children},{[Measures].[Net Revenue]}),1);

    Mack

  • Hi Mack,

    Your a diamond among the gems....fixed it...using your reply...gone back and determined the first child and created the set...and then did a tail on it....

    -- Previous 3 month sales using tail

    WITH MEMBER [Measures].[PrevThreeMonthSales] AS 'SUM(Tail([Order Date].[CalendarHierarchy].CurrentMember.Parent.Children(0):[Order Date].[CalendarHierarchy].CurrentMember,3), [Measures].[Sales Amount])'

    SELECT {[Measures].[Sales Amount], PrevThreeMonthSales} ON 0,

    [Order Date].[CalendarHierarchy].[Month] On 1

    FROM MyFirstCube

  • Hi Mack ,

    I'm on a roll...even simpler...using the "null" value brings back the first member of a set on the same level as the current member...only works if you use the range : (colon)

    -- Previous 3 month sales using tail and "null"

    WITH MEMBER [Measures].[PrevThreeMonthSales] AS 'SUM(Tail(null:[Order Date].[CalendarHierarchy].CurrentMember,3), [Measures].[Sales Amount])'

    SELECT {[Measures].[Sales Amount], PrevThreeMonthSales} ON 0,

    [Order Date].[CalendarHierarchy].[Month] On 1

    FROM MyFirstCube

    Cheers

    Robin

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply