July 23, 2009 at 12:34 pm
I'm trying to create distinct averages for each day of the week, something like the code below except this calulates an overall average for all days. I want to create a seperate distinct avg for Monday, Tuesday, etc.
Is this possible?
WITH
MEMBER [Measures].[Avg Per Day] AS
Avg(
[TimeDim].[Date].[Date],
[Measures].[Transaction Count]
)
SELECT {[Measures].[Transaction Count], [Measures].[Avg Per Day]} ON COLUMNS ,
[TimeDim].[Day Of Week].[Day Of Week] ON ROWS
FROM [My Cube]
July 23, 2009 at 3:33 pm
When you specify [TimeDim].[Date].[Date], you're resetting the context for your measure to all dates. You need to filter your dates in your member expression by the current day of the week. something like WITH
MEMBER AvgPerDay AS
Avg(
Filter( [Due Date].[Dim Time].Children * [Due Date].[Day Number Of Week].CurrentMember, [Measures].[Fact Reseller Sales Count])
, [Measures].[Fact Reseller Sales Count]
)
SELECT { [Measures].[Fact Reseller Sales Count], [AvgPerDay] } ON 0
, [Due Date].[Day Number Of Week].Children ON 1
FROM [Adventure Works DW]
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
July 23, 2009 at 3:36 pm
I came up with an even simpler solution. WITH
MEMBER AvgPerDay AS
Avg(
[Due Date].[Dim Time].Children * [Due Date].[Day Number Of Week].CurrentMember
, [Measures].[Fact Reseller Sales Count]
)
SELECT { [Measures].[Fact Reseller Sales Count], [AvgPerDay] } ON 0
, [Due Date].[Day Number Of Week].Children ON 1
FROM [Adventure Works DW]
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
July 24, 2009 at 6:24 am
Thank you. The code worked great.
I'm still a little confused though as to how doing the Cartesian Product helps here and hoping you or someone can add a little more explaination.
July 24, 2009 at 7:00 pm
I'm still learning MDX myself, so I'm not sure I can explain why it is necessary. I knew that you shouldn't be getting the exact same average for each day, if the day of the week was being included in the context. Since you were getting the same average, the obvious conclusion was that the current day of the week wasn't included in the context and that you could make sure it was included by adding a reference to the .currentmember.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply