Min Date based on Sum amount

[muthukumarbalu91]

Hi Team,

I have data as below,

Date	Row	Value
1/1/2022	1	10
1/1/2022	2	23
1/1/2022	3	45
1/1/2022	4	576
1/1/2022	2	23
1/1/2022	5	67
2/1/2022	1	1
2/1/2022	2	5
2/1/2022	3	6
2/1/2022	4	4
2/1/2022	2	90
2/1/2022	5	98
3/1/2022	1	566
3/1/2022	2	343
3/1/2022	3	32
3/1/2022	4	23
3/1/2022	2	54
3/1/2022	5	6

expected output from QS is Sum(Value) which is MIN and MAX as below,

	Date	Value
MinValueDate	2/1/2022	204
MaxValueDate	3/1/2022	1972

How to achieve it in Qlik, Please throw some ideas.

thanks in advance

@sunny_talwar 

[oskartoivonen]

Your example doesn't seem to be logical, if the idea is to locate which date had the smallest value and which date had the max value, then MinValueDate should be 2.1.2022 (value = 1) and MaxValueDate should be 1.1.2022 (value = 576).

But to do this in practice, you should use the aggregation function FirstSortedValue. Ask FirstSortedValue for a date and sort the data by value, the sorting direction can be reversed to get min and max dates respectively.

[muthukumarbalu91]

oskartoivonen

thanks for you reply.

Here the idea is to aggregate the value at each date level, then show which date has Min and Max value.

I have tried your suggestion and my set expression as follow,

FirstSortedValue(AGGR(Sum(Value),Date),Date)

This is not giving the results. Correct me if anything wrong here

[Pierrick · Accepted solution]

Hello @muthukumarbalu91,

You were almost there.

The function first takes the field you want and then the sorting method. (write in the help.qlik.com)

So your expression for min is : FirstSortedValue(Date,AGGR(Sum(Value),Date))

And max is : FirstSortedValue(Date,-AGGR(Sum(Value),Date))