Displaying the Top Salesperson in a Textbox

[Charles_Bannon]

Do you need to display the top performance person’s name in the dashboard?  For example, you need to display the name of top salesperson.  It is easy to do so with a table displaying the list of sales reps with sales amount, sorted from the highest value, but you just need to display the top sales person’s name in your dashboard. Here is how to do it.

As is almost always the case with QlikView, there is more than one way to accomplish this task. The first way is to use the firstsortedvalue and aggr functions. 

firstsortedvalue( Salesperson, -Aggr(sum([Sales Amt]),Salesperson)) 

The expression first calculates the Sum([Sales Amt]) and aggregates it by Salesperson. The firstsortedvalue function then looks at the all the Sales Amt values and, because we are using a minus sign in front of the sort value, displays the Salesperson with the top Sales Amt. 

Another way to accomplish the task is to use the functions Rank, Aggr and Only.

only(if(aggr(Rank(Sum([Sales Amt])),Salesperson)=1,Salesperson))

This approach is a little more complicated but equally as effective. First, the expression takes the Sum([Sales Amt]) and aggregates that by Salesperson. Next, a rank is created based on the aggregated Sales Amt. The expression then takes only the Salesperson with a rank of one and displays it in the textbox.

Really, it is just that easy. I wrote a technical brief that outlines this process in more detail. Along with the technical brief I included a QVW and a small Excel file and a video.  You can access the files here,

Good Luck and Happy Qliking!