topic Re: First and second minimum in QlikView

First and second minimum

andreip21 — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

i have the following database:

Product ID	Price Start	Price End	Duration	Sale Price
1	1 aug	3 aug	3	20
1	4 aug	10 aug	7	15
1	11 aug	11 aug	1	20
1	12 aug	15 aug	4	30
1	16 aug	28 aug	13	35

how do i get the minimum and the second minimum + duration in this format:

Product ID	First min	Duration First min	Second min	Duration Second min
1	15	7	20	4

Thanks a lot,

Regards,
Andrei

jyothish8807 — Tue, 28 Aug 2018 08:44:38 GMT

Hi Andrei,

Please refer the attached.

Br,

tresB — Tue, 28 Aug 2018 09:07:58 GMT

This might not work for multiple Product IDs. In such cases, you might have to use FirstSortedValue() like in attached.

FirstSortedValue(DISTINCT Aggr(Sum(Duration),[Product ID], [Sale Price]), [Sale Price],2)

andreip21 — Tue, 28 Aug 2018 10:25:21 GMT

ok, great, but i have one more question:

can i combine firstsortedvalue with a condition?

let's say, the first and second minimum only after 11 aug.

and the results will be 20 with Duration 1 and 30 with Duration 4.

tresB — Tue, 28 Aug 2018 10:36:15 GMT

You can use set analysis within forstsortedvalue() for the same. PFA