QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

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

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?

MVP

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

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

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.