topic Re: Set analysis with aggregation in QlikView

Set analysis with aggregation

Tue, 12 Feb 2013 15:20:25 GMT

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

Re: Set analysis with aggregation

SunilChauhan — Tue, 12 Feb 2013 15:22:30 GMT

use below in your expressions

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

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

hope this helps

Re: Set analysis with aggregation

Tue, 12 Feb 2013 15:27:32 GMT

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

Tue, 12 Feb 2013 15:31:46 GMT

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.

Re: Set analysis with aggregation

Tue, 12 Feb 2013 15:51:50 GMT

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

Tue, 12 Feb 2013 16:57:10 GMT

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

karthikdaibala — Tue, 21 Mar 2017 03:17:40 GMT

Hi Davide,

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

Thanks

Karthik