Ranking Problem

[#1]

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,

FS

[swuehl · Accepted solution]

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

[#3]

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

Thnks Stefan!