Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

jchang_snwl
New Contributor II

textbox shows field value with max rate in a group

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

3 Replies
MVP
MVP

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

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

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

MVP
MVP

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

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.

Community Browser