Problems with the aggregate function on a SP1 AS 2005 installation

We are using the Aggregate function in order to calculate totals for month members on the row axis. This calculated member is supposed to calculate Totals for every measure on the column axis.

The problem is that this works fine with simple calculated members like the division of two measures. But If a calculated member includes a time function like YTD or Parallelperiod aggregate returns null.

If I use Sum on a calculated member with YTD everything works fine.

This must be a bug?

Regards

Thomas Ivarsson

Hi Thomas,

Could you provide more details of how you're using Aggregate() - I suspect that this behavior might be by design? Aggregate() didn't support calculated measures in AS 2000 - and, according to Mosha, in AS 2005, Aggregate() works with a calculated measure by "switching solve orders":

http://sqljunkies.com/WebLog/mosha/archive/2005/10/11/mdx_functions_as2005.aspx

>>

Aggregate function can also work when the current measure is calculated measure, by switching solve orders with it. I.e. If the current measure is Ratio: Sales/Cost, and we are computing Aggregate({USA, Canada}), then instead of error, it will return Aggregate({USA, Canada},Sales)/Aggregate({USA, Canada},Cost).

>>

This will work with ratios, since the numerator and denominator will first be aggregated, then divided. But in your scenario, I'm not sure that the time functions can be applied after aggregation.

|||

Hi Deepak. Thank's for the reply. I have been away for a few days but here is an explanation.

We use aggregate for totals like this: Aggregate(Existing Intersect({{Extract({[DaysThisMonth]},[Time].[Time_Calendar])}},{[DaysThisMonth]}))

[DaysThisMonth] is a set with the days fo the current month. This set is also used for the rows and the total by the aggregate function calculates the totals for different measures on column axis.

If I have a calculated member like [Measures].[Sales]/[Measures].[Budget] everythin works fine with the total for [DaysThisMonth]. But if I use a calculation like Sum(YTD(), [Measures].[Anything] I will get null for the total.

If you have any ideas I will be grateful.

Kind regards

Thomas Ivarsson

|||

Thomas,

Since I'm not sure what's going on between Existing, Intersect and Extract, so I'm using that part as is.

For a calculated measure like you cited: Sum(YTD(), [Measures].[Anything]), it seems that Sum() across [DaysThisMonth] is not the aggregation you'd want - perhaps it should be computed for the last day? In that case, you could scope the calculation of the [DaysThisMonth] member separately for such YTD measures, like:

Create Member CurrentCube.[Time].[Time_Calendar].[DaysThisMonthAgg] as

Aggregate(Existing Intersect({{Extract({[DaysThisMonth]},[Time].[Time_Calendar])}},{[DaysThisMonth]}));

Scope ({[Time].[Time_Calendar].[DaysThisMonthAgg]}, {SetOfYTDMeasures});

this = ([Measures].CurrentMember,Tail(Existing

Intersect({{Extract({[DaysThisMonth]},[Time].[Time_Calendar])}},{[DaysThisMonth]})).Item(0).Item(0));

End Scope;

|||

Thank's Deepak. Existing, Intersect and Extract are generated by ProClarity professional 6.1. I have also tried with only Aggregate({[DaysThisMonth]},[Measures].Currentmember) with the same null-result for calculated measures with MDX-time-functions.

Aggregate is used for a total of each of the days within the last month({DaysThisMonth}).

I will try your example.

Kind regards

/Thomas

|||

Deepak.

Here is the complete MDX: It is a simplified cube from the Adventure Works projekt.

WITH MEMBER [Due Date].[CalendarYearDate].[All].[ Grand Total] AS 'AGGREGATE( EXISTING INTERSECT( { { EXTRACT( { DESCENDANTS( [Due Date].[CalendarYearDate].[Year].&[2004], [Due Date].[CalendarYearDate].[Month] ) }, [Due Date].[CalendarYearDate] ) } }, { DESCENDANTS( [Due Date].[CalendarYearDate].[Year].&[2004], [Due Date].[CalendarYearDate].[Month] ) } ) )', SOLVE_ORDER = 1000 SELECT { [Measures].[Sales / Product Cost], [Measures].[Sales Amount], [Measures].[Sales YTD Aggregate], [Measures].[Sales YTD Sum], [Measures].[TestParallelPeriod] } ON COLUMNS ,

{ ( [Due Date].[CalendarYearDate].[All].[ Grand Total] ), { DESCENDANTS( [Due Date].[CalendarYearDate].[Year].&[2004], [Due Date].[CalendarYearDate].[Month] ) } } ON ROWS

FROM [Adventure Works Sales Cube]

WHERE ( [Customer].[CustomerGeography].[All] )

These are my calculated members:

CREATE MEMBER CURRENTCUBE.[MEASURES].[Sales YTD Aggregate]

AS Aggregate(YTD([Due Date].[CalendarYearDate]),[Measures].[Sales Amount]),

FORMAT_STRING = '### ### ### ###',

NON_EMPTY_BEHAVIOR = { [Sales Amount] },

VISIBLE = 1 ;

CREATE MEMBER CURRENTCUBE.[MEASURES].[Sales YTD Sum]

AS SUM(YTD([Due Date].[CalendarYearDate]),[Measures].[Sales Amount]),

FORMAT_STRING = '### ### ### ###',

NON_EMPTY_BEHAVIOR = { [Sales Amount] },

VISIBLE = 1 ;

CREATE MEMBER CURRENTCUBE.[MEASURES].[Sales / Product Cost]

AS IIF([Measures].[Total Product Cost]<> 0,[Measures].[Sales Amount]/[Measures].[Total Product Cost],Null),

FORMAT_STRING = '### ### ### ###',

NON_EMPTY_BEHAVIOR = { [Total Product Cost], [Sales Amount] },

VISIBLE = 1 ;

CREATE MEMBER CURRENTCUBE.[MEASURES].[TestParallelPeriod]

AS (ParallelPeriod([Due Date].[CalendarYearDate].[Year],1,[Due Date].[CalendarYearDate].Currentmember),[Measures].[Sales Amount]),

FORMAT_STRING = '### ### ### ###',

NON_EMPTY_BEHAVIOR = { [Sales Amount] },

VISIBLE = 1 ;

The problem is that I get null for the calculated members with MDX time functions. Clearly the two ytd members would only return sums of cumulative values that does not make sense. But the Parallelperiod member would be interesting.

Regards

/Thomas