topic Re: FirstSortedValue with values over a range in App Development

FirstSortedValue with values over a range

NickBentley — Tue, 28 Nov 2023 05:39:30 GMT

I am trying to use FirstSortedValue in QlikView to give me the customer that has sold the most amount of units over the 3 week period and the total number of units sold.

The answer should be = 13

The customer should be customer A but I am just after the value '13' at this stage.

Thanks in advance! 🙂

Customer	Week	Units_Sold
Customer A	1	4
Customer A	2	5
Customer A	3	4
Customer B	1	9
Customer B	2	1
Customer B	3	2
Customer C	1	2
Customer C	2	3
Customer C	3	6
Customer D	1	2
Customer D	2	1
Customer D	3	3

Re: FirstSortedValue with values over a range

BrunPierre — Tue, 28 Nov 2023 06:07:15 GMT

Try

=FirstSortedValue(
Aggr(Sum(Units_Sold), Customer),
-Aggr(Sum(Units_Sold), Customer)
)

Re: FirstSortedValue with values over a range

NickBentley — Tue, 28 Nov 2023 06:12:01 GMT

Thanks BrunPierre, solution works perfectly!

Re: FirstSortedValue with values over a range

Jeevayswaran — Tue, 28 Nov 2023 06:14:26 GMT

Hi, Please try this:

if you want to get Value try this:

FirstSortedValue(aggr(sum(alt(Units_Sold,0)),Customer),-aggr(sum(alt(Units_Sold,0)),Customer),1)

if you want to get Customer name try this:

FirstSortedValue(Customer,-aggr(sum(alt(Units_Sold,0)),Customer),1)

and make sure that there is no null value in your data in Customer Name.

Re: FirstSortedValue with values over a range

NickBentley — Fri, 01 Dec 2023 01:40:28 GMT

Thank you Jeevayswaran, I have used this to get the results I was after for the customer name 🙂