
Expression to determine value of field1 for which field2 is max
Jonathan Dienst Mar 21, 2012 8:23 AM (in response to Marc Donckers)Hi
Use firstsortedvalue(CustomerName, CustomerSales)
From the manual:
firstsortedvalue( [{set_expression}][ distinct ] [ total [<fld {, fld}>]] expression [, sort_weight [, n]])
returns the first value of expression sorted by corresponding sortweight when expression is iterated over the chart dimension(s). Sortweight should return a numeric value where the lowest value will render the corresponding value of expression to be sorted first. By preceding the sortvalue expression with a minus sign, the function will return the last value instead. If more than one value of expression share the same lowest sortorder, the function will return null. By stating an n larger than 1, you will get the nth value in order.
Regards
Jonathan

Expression to determine value of field1 for which field2 is max
Marc Donckers Mar 21, 2012 9:00 AM (in response to Jonathan Dienst )Hi Jonathan,
Thanks for the reply. I think I am not quite there yet. For each CustomerName, there are multiple values for CustomerSales. Using the expression as you suggested, I get the CustomerName with the largest single field value for CustomerSales. What I need is the customer for which SUM(CustomerSales) is largest. How should I adapt the expression?
Thanks for the help!
Marc.

Expression to determine value of field1 for which field2 is max
Jonathan Dienst Mar 21, 2012 4:07 PM (in response to Marc Donckers)Marc
The sort weight parameter can be any expression, so you can use Sum(CustomerSales) for the sort weight. But because firstsortedvalue is an aggregation expression, I think the sum must be enclosed in an aggr expression, like this:
firstsortedvalue(CustomerName, aggr(Sum(CustomerSales), CustomerName))
Regards
Jonathan



Expression to determine value of field1 for which field2 is max
Marc Livingston Mar 21, 2012 4:10 PM (in response to Marc Donckers)=aggr(if(rank(sum({1}[SalesAmount]))=1,Customer),Customer)

Re: Expression to determine value of field1 for which field2 is max
harishre Dec 16, 2012 9:09 AM (in response to Marc Donckers)Hi MarcD,
Did you get any solution for this ? I am also having similar problem.
Jonathan, The solution provided by you always shows the row which has maximum sales.
For Ex:
CustName Sales
A 100
A 200
B 400
A 200
C 300
Output will be B. where I need it as A , since A has Total Sales as 500
marcsliving, Solution provided by you worked for the overall records, but when i apply a filter for 2 CustNames(for A and C) the result is "" , I am trying to understand the expression you provided. Please help me understand. Thanks..
=aggr(if(rank(sum({1}[Sales]))=1,CustName),CustName)
Thanks,
Rekha