December 11, 2012 at 4:28 am
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
December 11, 2012 at 4:35 am
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
December 11, 2012 at 5:19 am
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
December 11, 2012 at 6:43 am
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