Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello Qlik Gurus,
Say I have three columns Date, Sales Person, Sales.
How do I show the worst performing sales person by sales and date in a text box.
I can show top performing sales person by
=firstSortedValue([Customer Name], -AGGR(SUM(Sales),[Customer Name]))
Any idea how to do it for worst performing sales person.
-Roy
May be this? (Removed the negative sign in front of the AGGR() function:
=FirstSortedValue([Customer Name], AGGR(SUM(Sales),[Customer Name]))
Hi Reddy
Check the attached example of worst 3 sales person chart
To change this to top sales person change the dimension
to
=if(Aggr(rank(sum(Sales)),Customer,SalesPerson)=1,SalesPerson)
May be this? (Removed the negative sign in front of the AGGR() function:
=FirstSortedValue([Customer Name], AGGR(SUM(Sales),[Customer Name]))
Sunny is right , try taking out that minus ( - ). It tells QlikView to sort descending. All you want to do here is to sort ascending. You mentioned you want to show worst performing salesperson by Sum(Sales) and Date in a text box. If that is the case, be sure to add Date dimension in your AGGR expression. Because Salesperson A can be the worst performer (lowest Sum(Sales)), but he/she may not have the lowest sale on a single day. Adding date dimension can give you the salesperson who had lowest sale in a day.
=firstSortedValue([SalesPerson], AGGR(SUM(Sales),Date,[SalesPerson]))