MDX: Calculating avg action time and change over time, for top 5 actions
I have an "Actions" cube. The dimensions are "time" and "action ID" and the measurements are "number of actions" and "total time" and a calculated measurement "average action time". I am trying to calculate the top 5 actions by avg time, and the show the change from the previous day. I can do this in two separate queries:
SELECT {[Measures].[Avg Action Time]} ON COLUMNS,
NON EMPTY TopCount( except([Action ID].members, {[Action ID].[All Action IDs]}), 5, [Measures].[Avg Action Time]) ON ROWS
FROM Actions
WHERE [Time].[2005].[1];
and:
WITH MEMBER [Measures].[Change] AS
([Time].CurrentMember, [Measures].[Number of Actions]) / (ParallelPeriod ([Day], 1, [Time].CurrentMember), [Measures].[Number of Actions]),
FORMAT_STRING = 'Percent'
SELECT [Measures].[Change] on COLUMNS,
NON EMPTY [Time].[2005].[1].children on ROWS
FROM [Actions];
But I Can't figure out how to combine them into one MDX Query. I tried:
WITH MEMBER [Measures].[Change] AS
([Time].CurrentMember, [Action ID].CurrentMember, [Measures]开发者_运维问答.[Avg Action Time]) / (ParallelPeriod ([Day], 1, [Time].CurrentMember), [Action ID].CurrentMember, [Measures].[Avg Action Time]),
FORMAT_STRING = 'Percent'
SELECT {[Measures].[Avg Action Time], [Measures].[Change]} ON COLUMNS,
NON EMPTY TopCount( except([Action ID].members, {[Action ID].[All Action IDs]}), 5, [Measures].[Avg Action Time]) ON ROWS
FROM Actions
WHERE [Time].[2005].[1];
but change percentage is always infinity, so it clearly isn't calculating the right thing. What is the correct query?
The problem was that I didn't have enough data in my fact table. This query works for me for a different time range.
WITH MEMBER [Measures].[Change] AS
([Time].CurrentMember, [Measures].[Number of Actions]) / (ParallelPeriod ([Day], 1, [Time].CurrentMember), [Measures].[Number of Actions]),
FORMAT_STRING = 'Percent'
SELECT {[Measures].[Avg Action Time], [Measures].[Change]} ON COLUMNS,
NON EMPTY TopCount( except([Action ID].members, {[Action ID].[All Action IDs]}), 5, [Measures].[Avg Action Time]) ON ROWS
FROM Actions
WHERE [Time].[2005].[1].[2];
精彩评论