topic Top 10 Year On Year in QlikView

Top 10 Year On Year

dmac1971 — Wed, 13 Nov 2013 15:52:41 GMT

I have two tables, one for 2012 and one for 2013 showing top 10 customers. The problem is that the 2012 will only show the top 9 customers, I suspect the reason is that the 10th customer for 2012 is not in the top 10 for 2013 so its now being shown? Any ideas how to fix, everything else is working correctly!!

Re: Top 10 Year On Year

Anonymous — Wed, 13 Nov 2013 16:10:15 GMT

Hello,

For your Top 10 tables, did you use Dimension limits or a set analysis ?

Quentin

Re: Re: Top 10 Year On Year

dmac1971 — Wed, 13 Nov 2013 16:17:04 GMT

Dimension Limits :

=if(aggr(rank(sum([Sales])),[Customer])<=10,[Customer])

The expression then limits sales to 2012 :

=Sum({<[Year] = {"2012"}>}[Sales])

Should I be limiting the sales year within the dimension?

Re: Re: Re: Top 10 Year On Year

Anonymous — Wed, 13 Nov 2013 16:31:36 GMT

If you are using a straight table on QV 11, you can do this :

- In dimension tab, choose only Customer dimension.

- In dimension limits, choose to restrict the values and show only the 10 largest values and not show the others

- Then keep your expression.

Re: Re: Top 10 Year On Year

dmac1971 — Wed, 13 Nov 2013 16:37:17 GMT

Perfect, thanks QB!!!!

Re: Top 10 Year On Year

Wed, 13 Nov 2013 16:39:46 GMT

Did you try:

=if(aggr(rank(sum({<[Year] = {"2012"}>} [Sales])),[Customer])<=10,[Customer])

You select the top 10 customers based on the selection, and you sum your sales based on 2012.

Fabrice