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'
