Expression to determine value of field1 for which field2 is max

[#1]

Hi,

I am trying to figure out which exprssion to use to determine the field value of field1 for which the field value of field2 is maximum. Concretely: field1 is CustomerName. Field2 is CustomerSales. What I need is an expression to find the CustomerName which has the maximum sales. Of cours, I could just make a table, order the customers based on their sales from high to low and look at the customer in the top row. However, I would like to have an expression to determine the customer name, so I can use the customer name in further expressions. Any ideas?

Thanks,

Marc.

[jonathandienst]

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 sort-weight when expression is iterated over the chart dimension(s). Sort-weight should return a numeric value where the lowest value will render the corresponding value of expression to be sorted first. By preceding the sort-value expression with a minus sign, the function will return the last value instead. If more than one value of expression share the same lowest sort-order, the function will return null. By stating an n larger than 1, you will get the nth value in order.

Regards

Jonathan

[#3]

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.

[jonathandienst]

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

[#5]

=aggr(if(rank(sum({1}[SalesAmount]))=1,Customer),Customer)

[#6]

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