FirstSortedValue with values over a range

[NickBentley]

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

[BrunPierre · Accepted solution]

Try

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

[NickBentley]

Thanks BrunPierre, solution works perfectly!

[Jeevayswaran]

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.

[NickBentley]

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