Displaying a specific record by finding the max of a field

[#1]

I want to do this in a text object.

How do I find the max value of a field, then display the corresponding value of that record under another field?

Ex: Find max(sales), then display the salesman responsible for that sale.

How about the second-place salesman?

[swuehl · Accepted solution]

You can probably use Firstsortedvalue() for this, maybe like

=FirstSortedValue( salesman, -aggr( sum(sales), salesman))

and for second place

=FirstSortedValue( salesman, -aggr( sum(sales), salesman),2)

[#3]

Thanks!  This is what I needed.  Just one question... What is the - operator in front of aggr for?

[swuehl]

It just negates the expression results of the aggr() function (you could of course also write: aggr( -sum(sales),salesman)

The FirstSortedValue() function returns the salesman with the smallest sort weight, if you want to return the largest / max(sales), use a negative sum of sales.

Hope this makes sense,

Stefan