Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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.
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.
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
Thanks, this worked great!
Thank you! What a handy function!
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?
Wonderful Q&A !!!!!!
Thanks ...