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

    Expression to determine value of field1 for which field2 is max

    Marc Donckers

      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
          Jonathan Dienst

          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

            =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