Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Top Ranked Salesperson's Name Next to Client

I have a chart with Clients on the left side.

Each client deals with multiple salespeople.

I'd like to create a straight table with CLIENTS on the left and a column with the #1 RANKED SALESPERSON for that client (the name as well as the sales amount).

I think this is possible using the aggr(rank( function, but can't get the syntax exactly right.

Any help much appreciated. Thanks in advance!

1 Solution

Accepted Solutions
Not applicable
Author

This seemed to work on some sample data (use your actual field names where needed):

Aggr(If(Rank(Sum(Sales))=1, Salesperson), Client, Salesperson)


EDIT: Made a change to the Rank() expression.

View solution in original post

6 Replies
Not applicable
Author

This seemed to work on some sample data (use your actual field names where needed):

Aggr(If(Rank(Sum(Sales))=1, Salesperson), Client, Salesperson)


EDIT: Made a change to the Rank() expression.

Not applicable
Author

Try FirstSortedValue function

So

Dimension = Client

Expression 1 = FirstSortedValue( Name_of_Salesperson , - Amount) to display the best Salesperson

Expression2 = FirstSortedValue( Amount , - Amount) to display the amount of the best salesperson

Regards

JJ

Not applicable
Author

Thanks, this worked great!

Not applicable
Author

Thank you! What a handy function!

Not applicable
Author

Great solution, also helped me a lot. Do you maybe know how to avoid problem with more than one value with same sort-order, when the function returns null?

Not applicable
Author

Wonderful Q&A !!!!!!

Thanks ...