6 Replies Latest reply: Mar 20, 2017 11:17 PM by karthik Daibala

# Set analysis with aggregation

Hi all,

I'm facing with a set analysis problem.

I want to compare for some given dimensions the same expression between the minimum and the maximum date in a pivot chart.

I try to explain better.

I have the following data model:

 Field A Field B Date Value 1 AA 01/01/2013 5 2 BB 02/01/2013 10 3 CC 03/01/2013 10 1 AB 04/01/2013 10 2 AB 05/01/2013 15 1 AC 06/01/2013 20

I want to obtain the following result:

 Field A sum({}Value) sum({}Value) 1 5 20 2 10 15 3 10 10

But the above set analysis does not work because checks the minimum and the max between all date set and the result is not correct.

I try also with FirstSortedValue, but it does not allow to aggregate value in a pivot chart.

Any help?

Thank you very much in advance.

Davide

• ###### Re: Set analysis with aggregation

use below in your expressions

sum({<Date={\$(=min(Date))}>}Value)

sum({<Date={\$(=max(Date))}>}Value)

hope this helps

• ###### Re: Set analysis with aggregation

Sorry, I post the wrong syntax.

I already used the expression you suggest but the result is not that I want.

The above set analysis search the min(date) between all date and not between the date relative to each Field A.

• ###### Re: Set analysis with aggregation

FirstSortedValue works but if I have a pivot table with different dimensions on which I want to aggregate, without the sum aggregation, I'm not be able to get the same result.

For example:

 Field Z Field A sum({}Value) sum({}Value) 1 1 5 20 1 2 10 15 1 3 10 10

Aggregating on Field Z with the SUM functions I should get:

 Field Z sum({}Value) sum({}Value) 1 25 45

With FirstSortedValue I don't get the above result.

• ###### Re: Set analysis with aggregation

FirstSortedValue return to me the following if I aggregate on Field Z:

 Field A FirstSortedValue(Value,Date) FirstSortedValue(Value,-Date) 1 5 20

and not the sum of the values (25 and 45).

What QV version have you?

• ###### Re: Set analysis with aggregation

I get the expected result with the following formulas:

=sum(aggr((FirstSortedValue(Value,Date)),"Field A","Field Z", "list_of_dimensions_in_pivot_table")).

Thanks to all.

Davide

• ###### Re: Set analysis with aggregation

Hi Davide,

i am trying to do the same, would you be able to explain this with some example.

Thanks

Karthik