Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

mdx tail function not working on currentmember Expand / Collapse
Author
Message
Posted Tuesday, December 11, 2012 4:28 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, November 21, 2014 4:04 AM
Points: 69, Visits: 336
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
Posted Tuesday, December 11, 2012 4:35 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, January 13, 2014 7:34 AM
Points: 117, Visits: 499
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
Posted Tuesday, December 11, 2012 5:19 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, November 21, 2014 4:04 AM
Points: 69, Visits: 336
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
Posted Tuesday, December 11, 2012 6:43 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, November 21, 2014 4:04 AM
Points: 69, Visits: 336
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 »

Add to briefcase

Permissions Expand / Collapse