topic Re: Min Date based on Sum amount in App Development

Min Date based on Sum amount

muthukumarbalu91 — Thu, 08 Sep 2022 10:44:48 GMT

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

Re: Min Date based on Sum amount

oskartoivonen — Thu, 08 Sep 2022 10:35:01 GMT

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.

Re: Min Date based on Sum amount

muthukumarbalu91 — Thu, 08 Sep 2022 11:50:35 GMT

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

Re: Min Date based on Sum amount

Pierrick — Thu, 08 Sep 2022 12:32:31 GMT

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))