textbox shows field value with max rate in a group

[Anonymous]

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

[swuehl]

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?

[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%')

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

[swuehl]

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.