3 Replies Latest reply: Feb 12, 2016 6:51 PM by Stefan Wühl RSS

    textbox shows field value with max rate in a group

    Josh Chang

      Hello,

       

      I'm trying to find a expression for a textbox which shows the "Product" which has the max returned rate.

      The example is

      Product

      S/N

      Shipped Date

      Returned Date

      A

      113

      01/2014

      03/2015

      B

      124

      01/2014

      04/2014

      B

      216

      03/2014

       

      A

      158

      05/2014

      01/2015

      C

      116

      02/2015

      07/2015

      D

      215

      08/2014

       

      B

      178

      07/2014

      09/2015

      C

      331

      12/2014

       

      E

      159

      08/2014

       

      D

      234

      08/2014

      04/2015

      H

      578

      07/204

       

      E

      421

      09/2015

       

      H

      138

      01/2015

       

      H

      169

      07/2015

      11/2015

      F

      217

      06/2015

       

      F

      290

      02/2015

       

      G

      314

      03/2015

       

      A

      578

      11/2015

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

      Return rate A = 2(returned)/3(shipped) = 33.3%

      Return rate B = 2(returned)/3(shipped) = 33.3%

      Return rate C = 1(returned)/2(shipped) = 50%

      Return rate D = 1(returned)/2(shipped) = 50%

      ….

       

      And the Textbox will show "B = 33.3%" because the latest returned date for B is later than A

       

       

      Thanks,

      Josh

        • Re: textbox shows field value with max rate in a group
          Stefan Wühl

          Maybe something like

           

          =FirstSortedValue(DISTINCT 

               Aggr( Product, Product, [S/N]) ,

               Aggr( -(Count(TOTAL<Product> [Returned Date]) / Count(TOTAL<Product> [Shipped Date]) +[Returned Date]/1E10), Product, [S/N])

          )

           

          Hm, there should be something more simple, or even calculcate the return rate in the script?

          • Re: textbox shows field value with max rate in a group
            Sunny Talwar

            May be this:

             

            =Num(FirstSortedValue(

              Aggr(

              Count({<[Returned Date] = {"=Len(Trim([Returned Date])) > 0"}>}[Returned Date]) / Count([Shipped Date]), Product), -Aggr(Max([Returned Date]), Product)), '##.00%')


            Capture.PNG

             

            But this is for Product H because H has the max returned date

              • Re: textbox shows field value with max rate in a group
                Stefan Wühl

                If you want to use my expression - I am assuming that [Returned Date] is NULL if there wasn't a return, not something that gets counted ( a blank or something else).

                 

                You can also use this expression:

                =Only(

                {<Product = {"=Rank(Count([Returned Date])/Count([Shipped Date])+Max([Returned Date])/1E10,4)=1"}>}

                Product)

                 

                I am adding in both expression a small number based on the max return date to the return ratio to break the tie, assuming that comparing ratios up to 0.00001 should be sufficent.

                 

                Both expressions will return product 'B', not the ratio. if you want to add the ratio, add this to the text box

                 

                =Num( Max( Aggr( Count([Returned Date]) / Count([Shipped Date]),Product)),'#0.00%','.',',')


                You can also show both top return rated products, using

                 

                =Concat(DISTINCT {<Product = {"=Rank(Count([Returned Date])/Count([Shipped Date]))=1"}>}Product,', ')

                 

                Attached Sunny's QVW with my expressions added.