5 Replies Latest reply: Dec 16, 2012 9:09 AM by harishre

# Expression to determine value of field1 for which field2 is max

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.

• ###### Expression to determine value of field1 for which field2 is max

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

• ###### Expression to determine value of field1 for which field2 is max

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

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

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

• ###### Re: Expression to determine value of field1 for which field2 is max

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