Query Ranking and Assigning Percentile

winiblues · Apr 17, 2010

I am trying to create a query which will rank each sku for each supplier by sales.
Once ranked then I want to be able to assign a percentile of 0.2 for the top 20% of skus for a given supplier, then 0.4 for the next 20% of skus for a given supplier and so on.

I've attached a screen shot of the table and desired query result. Note: screen shot is in excel for ease.

Any help would be much appreciated

Wini

John Big Booty · Apr 17, 2010

First up welcome to the forum.

The attached DB ranks and scores sales rep.s by their weekly sales results.

Check the two queries that are doing the work, it shouldn't be too difficult to modify them to achieve your desired goal.

winiblues · Apr 18, 2010

Hi John,

Thanks for the reply. I was able to get the query to rank the sales. However, I'm unsure as to how to assign a percentile.

Your example would work fine if there were only a few ranking, however I have rankings up to 100,000 for which I need to assign a percentile.

Example provided:
Expr1: IIf([rank]=1,200,IIf([rank]=2,150,IIf([rank]=3,100,50)))

Any suggestions on how this can achieved?

Thanks

Wini

John Big Booty · Apr 18, 2010

You will need to use a nested Iif And Statement, something along the lines of;

Code:

Iif([rank]=>1 AND [rank]=<10,0.2 , Iif([rank]=>11 AND [rank]=<20, 0.4, 0.6))

Of course given that you will not have the same number of records each time you will need to dynamically set your break point by comparing the [rank] with the total count of rank.

I've made some changes to the Sample DB to now calculate the percentage of sales. Perhaps you can make the next steps with that example.

Query Ranking and Assigning Percentile

winiblues

New member

Attachments

John Big Booty

AWF VIP

Attachments

winiblues

New member

John Big Booty

AWF VIP

Attachments

Similar threads

Users who are viewing this thread