topic Re: Ranking Problem in QlikView

Ranking Problem

Tue, 12 Jul 2011 23:32:18 GMT

Hi everyone and thanks in advanced for your time.

I'm trying to do what I would have thought a basic sales analysis, however I haven't been able to get arround some ranking problems.

I'm trying to get a texbox (It doesen't HAVE to be a text box, but it would be better) to show me the name of my top X salesman (ranked by sales amount). In a second text box I need to see that persons top X products sold (again, ranked by sales amount). Since each salesman could hypothetically sell different products in order for each salesman top X products to appear the first condition should be if he/she actually sold those products.

I know the explanation might have been a bit fuzzy, so Im including both my .qvw file and my original data in .xlsx

Any tips will be appreciated!

Cheers,

Re: Ranking Problem

swuehl — Wed, 13 Jul 2011 00:34:54 GMT

Hi Francisco,

I tried to develop a solution, but in my App Carol is #2 instead of Julian

I used

= only({<Salesman= {"=rank(sum(Amount),Salesman) =1"}>} Salesman )

for top Salesman retrieval and

=firstsortedvalue({<Salesman= {"=rank(sum(Amount),Salesman) =1"}>} Product, -1 * aggr(sum(Amount), Salesman, Product),1)

for the Top product of the top Salesman.

For second, third, just adapt the magic numbers (the -1 in second argument to firstsortedvalue is for descending ordering).

Please check if this is what you want, if you have any questions, I'll be glad to answer.

Regards,

Stefan

Re: Ranking Problem

Wed, 13 Jul 2011 16:01:48 GMT

Worked like a charm! And you're correct on the Carol-Julian thing as well .

Thnks Stefan!