开发者

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];
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜