Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi, I need to find which [Sales Person] has performed well in a country? Here is my input table
Country | State | City | Sales Person | sale |
---|---|---|---|---|
Argentina | Buenos Aires | Ayacucho | Pamela | 1200 |
Australia | New South Wales | Avalon | Anke | 3600 |
Australia | New South Wales | Lindfiled | Jacob | 3600 |
Austria | Vienna | Inner City | Corina | 3600 |
Austria | Vienna | Mid City | Corine | 5000 |
My output in qlikview:
Country | Largest Sales By SaleRep | FirstSortedValue(SalesPerson,-aggr(sum(Sale),Country,SalesPerson)) |
---|---|---|
5000 | Mike | |
Argentia | 1200 | Jan |
Australia | 3600 | |
Austria | 5000 | Mike |
So there is null value in my 2nd row. Hence how do I remove duplicate or how should I bring my top Performer with repeated value?
Any help appreciated?
As far as I know, first sorted value returns a blank/null when more than value has the same weight. So here two names could end up having the same sales and hence it is not able to return a value. You can pick one by changing the formula to FirstSortedValue(distinct SalesPerson,-aggr(sum(Sale),Country,SalesPerson))
Thanks for the reply Chander:)