topic Re: to pick TOP-20 values in QlikView

to pick TOP-20 values

Anonymous — Sun, 06 Sep 2015 11:04:29 GMT

Hi,

I'm using the following expression to pick TOP-20 values:

Sum({$<Brand={"=rank(sum({$<Brand=>} Quantity ))<=20"}>} Quantity)

Please, see the file attached.

It works fine except for some cases. For instance, if Brand No20 and Brand No21 have the same sum(Quantity), then QlikView picks only first 19 brands. My task is to select 21 brands in such cases. It means that if it's not possible to select exactly 20 brands, then the programm should select 21 but not 19. How is it possible?

Thank you in advance,

Larisa

Re: to pick TOP-20 values

lironbaram — Sun, 06 Sep 2015 11:23:27 GMT

just change your expression to

Sum({$<Brand={"=rank(sum({$<Brand=>} Quantity ),1,1)<=20"}>} Quantity)

which set the rank format to return the lower value for to identical values

so instead of returning 20-21 in your example it'll return 20 to both entities

Re: to pick TOP-20 values

Anonymous — Sun, 06 Sep 2015 12:13:14 GMT

Thanks a lot!