Multi-Level Ranking

[QUINABA]

Hello all,

Is there a way to rank the following using a select query? The following is an excerpt from a larger table (over 100,000 records).

Store Product Jan $ Feb $
NY Juice 491 147
NY Chicken 341 144
NY Milk 1,085 128
NY Cosmetics 431 37
NY Beef 344 68,731
NY Eggs 119 7,152
NY Fish 117 4,999
NJ Juice 104 2,377
NJ Chicken 30 48
NJ Milk 55,687 31
NJ Cosmetics 55,687 403
NJ Beef 4,050 481
NJ Eggs 1,926 335
NJ Fish 39 1,064
CA Beef 25 422
CA Eggs 327 337
CA Fish 43 117

Result would be:

Store Product Jan $ Feb $ Jan Rank Feb Rank
NY Juice 491 147 2 4
NY Chicken 341 144 5 5
NY Milk 1,085 128 1 6
NY Cosmetics 431 37 3 8
NY Beef 344 68,731 4 1
NY Eggs 119 7,152 6 2
NY Fish 117 4,999 7 3
NJ Juice 104 2,377 4 1
NJ Chicken 30 48 6 6
NJ Milk 55,687 31 1 7
NJ Cosmetics 55,687 403 1 4
NJ Beef 4,050 481 2 3
NJ Eggs 1,926 335 3 5
NJ Fish 39 1,064 5 2
CA Beef 25 422 3 1
CA Eggs 327 337 1 2
CA Fish 43 117 2 3

Any help will be greatly appreciated.

Thanks.

 

[QUINABA]

The sample does not look correct... how can can I align the data with the field name?

 

[SydneyGeek]

To present in spreadsheet format, push the data to Excel and use Colo's HTML maker utility (available from every page of this board) to make a display version.

BTW, what are you basing your ranking on? Absolute value? Sales in a category? Sales in a category AND State?

Denis

 

[QUINABA]

Thanks Denis for the reply, unfortunately we're no longer allowed to download here at work.

I'm basing the rank by state and category, so as a result I can see the top items (by highest sales) by state in every month (1 years worth).

 

[SydneyGeek]

Hi QUINABA,

I have spent so much time in Excel recently that I saw your post as an Excel question! OK, brain on... that's better

First, you are getting yourself into trouble with your data, if you create a new field for each month of sales. Although it means a fair bit of data rearranging, you're far better off having a Month field (and a Year field), and a Value field. This means that your data will be be vertical rather than horizontal, but reporting becomes much easier (for example, you can easily report by month, quarter, half-year or year, just be changing a query criterion.

Based on the above layout, you can do this:

Under the Store field, set to Sort Ascending
Do the same for the Month field.
Sort the Value field Descending.
Click the Sigma button on teh query design toolbar to show the Total row. Leave Shop and Month as Group (the default) but make Value either Max or Sum, depending on whether you want to see the biggest individual sale or the total monthly sales.

That should get you started.

Denis

 

[QUINABA]

I understand your concerns about the table being horizontal in setup but I need to present it that way. I get the sorting part but what I'm trying to achieve is to identify their rank.

Thanks.

 

[Judge]

You might have already figured this out by now, but here's my 2 cents...
When I've got something like this to do, I run each part as a separate query. I don't know how you are getting your $s but when you have them, you can rank them in separate queries. Then, however you identify the state (or whatever level you are differentiating on) you can link each of the tables by that field and pull down the appropriate $ and rank fields from each table to create a new table with the data displayed horizontally.