topic Re: Top n / Bottom n in QlikView

Top n / Bottom n

Wed, 10 Aug 2016 09:35:58 GMT

Morning,

I've read and tried a few suggestions on top n / bottom n on blogs / forums but none seem to work.

I have a calculated Expression which shows Week On Week performance based on last weeks sales and the week before. All I need is the chart table to only display the highest 20 and lowest 20 performance based on this expression.

Re: Top n / Bottom n

Anonymous — Wed, 10 Aug 2016 12:47:50 GMT

Hi,

Maybe you can try this :

In the chart properties "Sort" tab, select your expression and select "Expression" in sort by. Here you can sort Descending (from highest to lowest sales) or Ascending (from lowest to highest sales) and then enter the expression.

In the "Dimension Limits" tab, choose "Restrict which values are displayed ..." and select to show only the largest or smallest 20.

So, in fact, you need a table to show the highest 20 and an other table to show the smallest 20.

Re: Top n / Bottom n

marcus_sommer — Wed, 10 Aug 2016 12:48:11 GMT

If you used two objects (for example straight tables) you could set those top/bottom 20 within the limitation tab. Within one object it's more complicated and you would need a calculated dimension like this:

= dual(

if(aggr(rank(sum(Value), 4), Customer) < 21 or

aggr(rank(sum(Value), 4), Customer) > $(=count(distinct Customer)) - 20, Customer),

aggr(rank(sum(Value), 4), Customer))

and as expression simply: sum(Value).

- Marcus

Re: Top n / Bottom n

Wed, 10 Aug 2016 13:01:40 GMT

Sorry I forgot to include that I need to restrict the ranked performance where sales are greater than £2000.

Re: Top n / Bottom n

marcus_sommer — Wed, 10 Aug 2016 13:29:22 GMT

You could of course include further conditions, maybe in these ways:

sum({< Value = {">=2000"}>} Value)

sum({< Value = {"=sum(Value)>2000"}>} Value)

- Marcus

Re: Top n / Bottom n

Wed, 10 Aug 2016 14:46:35 GMT

Hi,

sum({<fieldname={''=aggr(rank(sum(sales),4),Sales")>}+fieldname={''=aggr(rank(-sum(sales),4),Sales")>}Sales)

if u want bottomu can use -sum(sales)

Re: Top n / Bottom n

Wed, 10 Aug 2016 15:25:14 GMT

Thanks for the replies so far. I've not had chance to 'play' around this afternoon with your suggestions.