First and second minimum

[andreip21]

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]

Hi Andrei,

Please refer the attached.

Br,

KC

[tresesco]

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]

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.

[tresesco · Accepted solution]

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