Showing posts with label aggregate. Show all posts
Showing posts with label aggregate. Show all posts

Friday, March 30, 2012

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

Saturday, February 25, 2012

Problems with Aggregate function in ProClarity

The filteremptyrowsand grand total doesn′t work for me in ProClarity 6.3 when I′m using aggregate function. I get the #Error measage in Grand Total.

Could you post the MDX that's generating the error? (View | MDX Editor)

Thanks,
Bryan

Problems with Aggregate Expressions using Scope

Hi, I'm having the following problem when creating a matrix report. I'm trying to get the number of companies contacted per person to display in each cell. I have 3 group rows,

The first group, grpPerson =Fields!PersonID.Value
The second, grpCompany =Fields!CompanyID.Value with parent group = grpPerson
The thrid, grpOffice =Fields!OfficeID.Value with parent group = grpCompany

I have spent numerous hours trying different combinations of adding row groups, adding column groups, tried every possible combenation of aggregate functions, and scops, each give me different results, neither of them are what I am expecting.
Below is an example output of the following expression that I have in my data cell:
=Count(Fields!PersonID.Value, ("grpCompany")

My expected result is the number of rows per person, for the company for each month. i.e person1 called company1 5 times in the month of Feb. So each office should show the same some in the Month of Feb06. I will use this sum later to get the percentage of offices that the person called each month, and this count will alow me to do that.

However, the result below is what's being displayed. It appears the show, in the top row for each company, the number of companies the person called, not filtered by month. Then for the other rows of the company, it's showing the total number of rows returned from the whole dataset. I have tried just about every aggregate function possible. Any help to solve this problem would be great! Thanks!

Feb06 | Mar06 | Apr06 | Total
person1 | company1 | office1 20 20 20 500
| office2 500 500 500 500
| office3 500 500 500 500
person2 | company1 | office1 15 15 15 500
| office2 500 500 500 500
| office3 500 500 500 500

I’m also trying to do this using one dataset without using an aggregate within the SQL query that returns the number of times a person contacted a company each month because it significantly slows it down. I'm trying to do this all within reporting services. Any help to solve this problem would be great! Thanks!
Have you tried just =Count(Fields!PersonID.Value)? When you specify a explicit scope in the aggregate, e.g. grpCompany, it will return the sum for that group, regardless of the month in this case.|||Yes, I have tried Count(Fields!PersonID.Value), and it will give me the correct sum for the specific office of the company for the month, not the company count as a whole for the month. Is this even possible?
|||In that case, you can add a subtotal to the Office row group (right click on the row group and select subtotal). Then in the cells under the subtotal row, you should get the company count for the month.|||Thanks for your help so far, but that will give me the total number of office rows in the query. I'm looking to retrieve the number of rows the query returned per person. ie. person1 had 5 companies, person2 had 15 companies ect.

In addition I would like each cell for the person to be able to display the total value. Once I can do that I will be able to divide the total number of person calls by the number of times a person called an office,