Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 mdx tail function not working on currentmember Rate Topic Display Mode Topic Options
Author
 Message
 Posted Tuesday, December 11, 2012 4:28 AM
 Valued Member Group: General Forum Members Last Login: Wednesday, December 18, 2013 8:13 AM Points: 69, Visits: 319
 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 salesWITH 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 1FROM MyFirstCubethis does not however using tail substitution: -- Previous 3 month sales using tailWITH 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 1FROM MyFirstCube all i get is the same value for the current member Question ? Can the tail function not create dynamic sets on currentmember
Post #1395031
 Posted Tuesday, December 11, 2012 4:35 AM
 SSC-Enthusiastic Group: General Forum Members Last Login: Yesterday @ 10:57 AM Points: 117, Visits: 497
 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 valueSo 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
Post #1395039
 Posted Tuesday, December 11, 2012 5:19 AM
 Valued Member Group: General Forum Members Last Login: Wednesday, December 18, 2013 8:13 AM Points: 69, Visits: 319
 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 tailWITH 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 1FROM MyFirstCube
Post #1395062
 Posted Tuesday, December 11, 2012 6:43 AM
 Valued Member Group: General Forum Members Last Login: Wednesday, December 18, 2013 8:13 AM Points: 69, Visits: 319
 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 1FROM MyFirstCube CheersRobin
Post #1395088

 Permissions