topic Ranking in QlikView

Ranking

Wed, 30 Nov 2011 10:52:00 GMT

Hi All,

I would like to ask you how can I populate Top 5, Top 10, Top 20, Top 30 till Top 100 Customers and Products.

I have sort the amount in descending and in presentation tab I have checked Max 100. In this way I can able to show 100 top customers. But I also have to show top 10, 20, 30... so on till 100.

Thanks.

Ranking

swuehl — Wed, 30 Nov 2011 11:09:10 GMT

Hi,

if you want to rank e.g. by sum(Sales), you could use a calculated dimension like

=aggr(if(rank(sum(Sales))<=5,Customer), Customer)

and expression

=sum(Sales)

Replace Customer with Products if needed and 5 with 10, 20,30 , 100.

Hope this helps,

Stefan

Re: Ranking

Wed, 30 Nov 2011 11:14:02 GMT

Hi,

Thanks for the quick response.

But then i have to change it always. Is there any alternative ways..?

Thanks.

Re: Ranking

swuehl — Wed, 30 Nov 2011 11:19:18 GMT

Not really sure what you are after,

you could use a variable with associated slider to change the X in TopX instead of hardcoding the X.

Or you could highlight the different X by color, something like this as background color expression in expression tab:

=if(rank(sum(Sales))<=5,green()

if(rank(sum(Sales))<=10, yellow(), red() ) )

(This are awful colors, just an example).

If you tell us a bit more detailed, what you are looking for, we can help you better.

Regards,

Stefan

Re: Ranking

Wed, 30 Nov 2011 11:39:20 GMT

Hi Stefan,

Can you please provide me with an example.

And can't we hardcode it.. Suppose I click on Top 10 it will give me Top 10 customers and like wise for other Top(20,30,40) customers.

Thanks.

Re: Ranking

swuehl — Wed, 30 Nov 2011 11:48:36 GMT

Well, I thought you could give me an example for how it should work or look like

Ok, here is a simple example with an input box variable to select the X in TopX for Customers.

Re: Ranking

Wed, 30 Nov 2011 12:01:34 GMT

Please find attached of the qvw file.

Thanks.

Re: Ranking

swuehl — Wed, 30 Nov 2011 12:21:29 GMT

You are using table boxes, which don't allow calculated dimension or expressions.

You can limit your data with a trigger action though, so I created one under settings -> document properties -> triggers for TopRank field.

If you select one value in TopRank, you now select the topX CustomerIDs.

To show how you could go on with my original approach, you'll need a table chart. So I created one, used the above mentioned calculated dimension, but with max(TopRankAggr) instead of the variable.

You need only one of the solutions, not both, of course.

Hope this helps,

Stefan

Re: Ranking

Wed, 30 Nov 2011 12:39:34 GMT

It works.

Thanks a ton Stefan...!!