Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
Try
=FirstSortedValue(
Aggr(Sum(Units_Sold), Customer),
-Aggr(Sum(Units_Sold), Customer)
)
Try
=FirstSortedValue(
Aggr(Sum(Units_Sold), Customer),
-Aggr(Sum(Units_Sold), Customer)
)
Thanks BrunPierre, solution works perfectly!
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.
Thank you Jeevayswaran, I have used this to get the results I was after for the customer name 🙂