Aggregate Expression

geogou1973 — Wed, 08 Dec 2021 16:13:54 GMT

Hello, I have the following table and i want to aggregate the results

ITEM	SUPPLIER	DATE	PRICE
101642	47922	22/01/2020	5,4
101642	59368	02/07/2020	5,61
101642	59368	27/10/2020	5,61
101642	59368	05/11/2020	5,61
101642	59368	18/11/2020	5,61
101642	59368	28/11/2020	5,61
101642	59368	22/01/2021	5,61
101642	59368	23/01/2021	5,61
101642	46739	16/06/2021	5,05
101642	46739	17/06/2021	5,05
101642	59368	18/06/2021	5,26
101642	59368	22/10/2021	5,26
101642	59368	23/10/2021	5,26

like this

ITEM	SUPPLIER	MIN DATE	MIN PRICE	MAX DATE	MAX PRICE
101642	46739	16/06/2021	5,05	17/06/2021	5,05
101642	47922	22/01/2020	5,4	22/01/2020	5,4
101642	59368	02/07/2020	5,61	23/10/2021	5,26

how can i handle this. I bring to the report the max and the min date with the following expression

aggr(MIN(DATE),NARTNA,NFOUNA) and DATE(aggr(MAX(DATE),NARTNA,NFOUNA))

how can i bring the correct prices ? Can anyone help me ?

Thank you in advanced.

Re: Aggregate Expression

chrismarlow — Thu, 09 Dec 2021 17:54:34 GMT

Hi,

If you are trying to get the prices that align to the min date and max date (rather than the min and max prices) you can use FirstSortedValue, so something like;

FirstSortedValue(PRICE,DATE)

and

FirstSortedValue(PRICE,-DATE)

Might be some complication as I don;t understand why you needed to use AGGR functions though (what are NARTNA,NFOUNA?).

Cheers,

Chris.

topic Re: Aggregate Expression in QlikView

Aggregate Expression

Re: Aggregate Expression