Need a formula to find the best sales person based on a formula

[AnnetteB1305]

Hello,

I need help getting a formula to find the best salesperson in the company.

The salesperson is a username field

The formula needs to be based on a new sales formula which is a sum of another formula which corrects credits as they are not held as minuses in the database.

So it is:-
//formula1:
If {database.type}='credit' then -{database.sale} else {database.sale}

then the other formula is sum ({formula1},{database.user})

I've tried messing about with Nth Largest but it keeps throwing an error about the first part needing to be a number however it is a number. Also not sure if Nth Largest is the correct formula

Any help is appreciated.

Thanks,

Annette

 

[kray4660]

it might not like the minus sign. I had this problem years ago. See if multiplying by a -1 works (i.e, (-1)*{database.sale}).

I hope this helps.

 

[AnnetteB1305]

Hi,

It's ok I found a solution

What I've done is put a sort on the group (salesperson) which is based on the sum of sales.

Then I've created a formula
@bestsalesperson
which is:-

if GroupNumber=1 then {Database.employeename}


Thanks for your help though

Annette

 

[MCuthill]

AnnetteB1305,

Alternatively, you could have altered your formula as per Kray's posting and using group selection, sorted descending by the sum of Formula1 and setting the report to return only the Top N groups, where N=1.

Glad you resolved your problem!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[AnnetteB1305]

Couldn't do that as needed to show all the salespeople but needed to have a formula for the best salesperson so I could use it to show various other info but thanks anyway.

 

[lbass]

If you insert a summary on {formula1} at the group level, you will activate the group sort feature, where you can choose "All" and descending order. Then you will see all sales people listed in descending order by sales. This is not available UNLESS you insert the summary (versus creating it in a summary formula).

-LB