mdx tail function not working on currentmember

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Data Warehousing » Analysis Services » mdx tail function not working on...

mdx tail function not working on currentmember

Rate Topic

Display Mode

Topic Options

Author

Message

robinrai3

Posted Tuesday, December 11, 2012 4:28 AM

Valued Member

Group: General Forum Members
Last Login: Thursday, June 13, 2013 9:18 AM
Points: 64, Visits: 281

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

Post #1395031

Mackers

Posted Tuesday, December 11, 2012 4:35 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 4:25 AM
Points: 117, Visits: 473

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

Post #1395039

robinrai3

Posted Tuesday, December 11, 2012 5:19 AM

Valued Member

Group: General Forum Members
Last Login: Thursday, June 13, 2013 9:18 AM
Points: 64, Visits: 281

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

Post #1395062

robinrai3

Posted Tuesday, December 11, 2012 6:43 AM

Valued Member

Group: General Forum Members
Last Login: Thursday, June 13, 2013 9:18 AM
Points: 64, Visits: 281

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

Post #1395088

« Prev Topic | Next Topic »

Permissions