Set analysis with aggregation

[#1]

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({<Date=(min(Date))>}Value)	sum({<Date=(max(Date))>}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

[SunilChauhan]

use below in your expressions

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

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

hope this helps

[#3]

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.

[#4]

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({<Date=(min(Date))>}Value)	sum({<Date=(max(Date))>}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({<Date=(min(Date))>}Value)	sum({<Date=(max(Date))>}Value)
1	25	45

With FirstSortedValue I don't get the above result.

[#5]

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?

[#6 · Accepted solution]

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

[karthikdaibala]

Hi Davide,

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

Thanks

Karthik