Skip to main content
Announcements
Qlik Connect 2024! Seize endless possibilities! LEARN MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
siddheshmane
Creator
Creator

Bottom 5 users

Hello all,

I am trying to display my bottom 5 users based on sales. These 5 users will be displayed in 5 different KPI cards. I was able to achieve the TOP 5 users by the FIRSTSORTEDVALUE().

FIRSTSORTEDVALUE(UserName, -AGGR(SUM({<MonthYear = {"$(=vCurrentMonth)"}>}GrossSales),UserName), 1)

 

My bottom 10 performers all have sales 0.. I want to show the bottom 5 in order of the names.

 

Thank you,

Siddhesh

1 Reply
Or
MVP
MVP

If there are ten users with the exact same sales (0) - how do you define "bottom 5" out of these ten? And how would you sort the worst customer and the 5th worst customer? This seems to be more of a logical question than a Qlik question, and FirstSortedValue() doesn't break ties in itself.

You could try breaking ties manually and quasi-randomly or arbitrarily, e.g. by adding Rand()/10000 to each aggregated sales total, which should break ties (but still be extremely unlikely to change the actual order of the customers). This will make the five objects populate randomly and re-populate randomly each time a selection is made, which could be kind of confusing to users.

You could also add some sort of arbitrary small value, e.g. assign an incremental number to each user and then add UserNumber/1000000 to your formula. This has the advantage of returning the same result each time but the order of the users will be completely arbitrary based on how you assigned these numbers in the first place.