Best salesrep based on sales as textbox

[Teppo_Ojala]

Hi,

I thought this was an easy task, but I didn't find a correct function easily. But anyway, I have a project where I want to write for example the best salesrep based on sales to one text box. In other words I would like to have a function where I give one dimension and one expression and as a result I will get the top 1 value. To the next text box I would like to have the value from this expression.

I made a very simple example. In this qvw I have couple of sales reps and their sales. As you can see "Ian" is the best seller. So to the first textbox I would like to get "Ian" with a formula - not hard coded And to next text object I would like to get his sales amount..

Thank you!!

[jvitantonio · Accepted solution]

Hi, For the sales rep name use formula

=MaxString( {<Sales ={$(#=max(Sales)) }>} SalesRep )

and for the sales amount use formula

=max(Sales)

although for a more complex architecture I would use:

= max(aggr(sum(Sales),SalesRep))

I hope this helps

[Teppo_Ojala]

Thanks. Exactly what I needed !

[jagan]

Hi,

Use this expression

=FirstSortedValue(  SalesRep,  -Aggr(Sum(Sales), SalesRep))

Hope this helps you.

Regards,

Jagan.