mdx tail function not working on currentmember

• robinrai3

  SSCrazy

  Points: 2810

  More actions

  December 11, 2012 at 4:28 am

  #267907

  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

• Mackers

  SSCrazy

  Points: 2935

  More actions

  December 11, 2012 at 4:35 am

  #1567368

  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

• robinrai3

  SSCrazy

  Points: 2810

  More actions

  December 11, 2012 at 5:19 am

  #1567389

  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

• robinrai3

  SSCrazy

  Points: 2810

  More actions

  December 11, 2012 at 6:43 am

  #1567413

  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