"Aggregate" of a calculated measure

Hi all,

I've got a calculated measure called [Measures].[Coverage]. I need a "report" with the following columns:

MAT = Moving Annual Total

Account Group, Coverage MAT, coverage for last MAT, and Variance.

The query should look something like this:

WITH

MEMBER

[Measures].[Coverage MAT]

AS

'AGGREGATE(LastPeriods(12, [Date].[Calendar Month Year].[Jun 2006]), [Measures].[Coverage])',

FORMAT_STRING = 'Percent'

MEMBER

[Measures].[Coverage LMAT]

AS

'AGGREGATE(LastPeriods(12, [Date].[Calendar Month Year].[Jun 2005]), [Measures].[Coverage])', //Note the 2005 year instead of 2006

FORMAT_STRING = 'Percent'

MEMBER

[Measures].[Variance]

AS

'IIF([Measures].[Coverage LMAT]=0,null,(([Measures].[Coverage MAT] - [Measures].[Coverage LMAT])/[Measures].[Coverage LMAT]))',

FORMAT_STRING = 'Percent'

SELECT

NON EMPTY {

[Account Group].[Account Group].[All].Children

} ON ROWS,

(

[Measures].[Coverage MAT],

[Measures].[Coverage LMAT],

[Measures].[Variance]

) ON COLUMNS

FROM [MyCube]

This query doesn't work because the Measures hierarchy appears more than once in the tuple and because you cannot aggregate calculated members. Any thoughts on how could I solve this issue. Thanks in advance.

It's unclear how Coverage MAT is computed from Coverage - but assuming that it's computed by averaging coverage over the last 12 months, you could substitute Avg() for Aggregate():

>>

WITH

MEMBER

[Measures].[Coverage MAT]

AS

'AVG(LastPeriods(12, [Date].[Calendar Month Year].[Jun 2006]), [Measures].[Coverage])',

FORMAT_STRING = 'Percent'

MEMBER

[Measures].[Coverage LMAT]

AS

'AVG(LastPeriods(12, [Date].[Calendar Month Year].[Jun 2005]), [Measures].[Coverage])', //Note the 2005 year instead of 2006

FORMAT_STRING = 'Percent'

MEMBER

[Measures].[Variance]

AS

'IIF([Measures].[Coverage LMAT]=0,null,(([Measures].[Coverage MAT] - [Measures].[Coverage LMAT])/[Measures].[Coverage LMAT]))',

FORMAT_STRING = 'Percent'

SELECT

NON EMPTY {

[Account Group].[Account Group].[All].Children

} ON ROWS,

{

[Measures].[Coverage MAT],

[Measures].[Coverage LMAT],

[Measures].[Variance]

} ON COLUMNS

FROM [MyCube]

|||

Hi Deepak,

I also tried to used the averages but the thing is that coverage can't be computed like this. Coverage is a calculated measure that gets the ratio of planned # calls with the actual number of calls. So if for example you have the following records:

month 1: Planned 3, Actual 0 -> Coverage 0 or 0%

month 2: Planned 3, Actual 0 -> Coverage 0 or 0%

month 3: Planned 3, Actual 9 -> Coverage 3 or 300%

Period: Planned 9 (3 + 3 + 3), Actual 9 (0 + 0 + 9) -> Coverage 1 or 100%

The problem is that the average and the real coverage is not the same (usually small difference, considerable in some cases), any ideas on how to deal with this difference?.

|||

A couple of questions then:

What is the exact definition of the Coverage calculated measure?|||

Deepak,

Coverage is the number of calls vs the number of planned calls for a certain period of time. Since you always want to follow your plan, you try that coverage is always 100%. The problem with my query is that I need to get the coverage for 2 different periods and then compare these two. With the average, we are calculating the coverage for each subperiod. Coverage is a calculated measure as I explained in the previous post, it is a very simple calculated measure (a division) but it takes care of a division by 0 (In case you have a plan for 0 calls).

I am using AS2005.

|||

One way would be apply Aggregate() separately to numerator and denominator:

MEMBER

[Measures].[Coverage MAT]

AS

AGGREGATE(LastPeriods(12, [Date].[Calendar Month Year].[Jun 2006]), [Measures].[ActualCalls])

/ AGGREGATE(LastPeriods(12, [Date].[Calendar Month Year].[Jun 2006]), [Measures].[PlannedCalls]),

FORMAT_STRING = 'Percent'