Tuesday, February 21, 2012

Scoped Calculations and Aggregate

I have a measure which is defined as 0 and then Scope'd for certain dimensions to be a percent of the total as below:

CREATE MEMBER [Measures].[Revenue Percent] AS 0;

SCOPE ([Measures].[Revenue Percent]);

THIS = ([Measures].[Gross Revenue]) / ([Measures].[Gross Revenue], [Area].[Sales Area].[All], [Direct Response].[DRS].[All])

SCOPE (Descendants([Area].[Sales Area].&[2], 1, SELF_AND_AFTER));

THIS = ([Measures].[Gross Revenue])

/ ([Measures].[Gross Revenue], [Area].[Sales Area].&[2], [Direct Response].[DRS].[All]);

END SCOPE;

END SCOPE;

This calculation works fine when I am browsing the cube as I would expect. I can create calculated members on my date dimension and the measure is calculated correctly using Aggregate({date1, date2, date3, etc}) function. However as soon as I change this calculation to use "relative" sets of members it simply returns 0.

So if I have the calcuation,

MEMEBER [Measures].[YTD Revenue Percent] AS AGGREGATE(YTD(date4), [Measures].[Revenue Percent])

it will always return 0. Even if I create the YTD portion as a set first and then aggregate that it still returns 0. However change the ytd(Date4) to something like {date1Big Smileate4} and the calculation will work.

Has anyone else seen this sort of behaviour, or can point me in the direction of information as to why it doesn't work?

Thanks in advance

Mike

PS: The date dimension is correctly figured and the member names date1 etc are just syntax for shortening the full date in the post.

{date1ate4} Should read {date1 : date4} - Sorry just seen that once it is posted.

No comments:

Post a Comment