Skip to Content

PAS - Calculating Ranking

Hi,

I'm trying to calculate a simple ranking in PAS but I'm having some problems with the RANK() command.

I create a small example using the JUICE database:

SET PERIOD JAN2006-JUN2007

CREATE SALES_Rank TEMPORARY LIKE SALES

SELECT Product

SELECT Channel 'C1'

SELECT Customer

ORDER Product ON SALES

CALCULATE SALES_Rank = RANK(Product)

After I run the above procedure, I get the following output:

2006

C1D1R1 C2D1R1 C3D1R1

CHANNEL C1

PRODUCT UPC1B1C1

SALES RANK 132 132 132

SALES 138.222,09 181.377,26 130.412,60

CHANNEL C1

PRODUCT UPC1B2C1

SALES RANK 84 84 84

SALES 280.826,57 223.492,68 216.759,33

CHANNEL C1

PRODUCT UPC1B4C1

SALES RANK 252 252 252

SALES 47.210,72 40.234,52 60.917,06

1 Page of Data Listed

As you can see the ranking in not correct because it's "ignoring" the others dimensions used by the variable SALES (I already try with the FULL keyword in the calculate, but I get the same wrong output).

The only way to get "correct" values, is if I do the calculation for each individual member of the dimensions (the others not used in the RANK command):

SELECT Product

SELECT Channel 'C1'

SELECT Customer 'C1D1R1'

ORDER Product ON SALES

CALCULATE SALES_Rank = RANK(Product)

SELECT Customer 'C2D1R1'

ORDER Product ON SALES

CALCULATE SALES_Rank = RANK(Product)

And the output:

2006

C1D1R1 C2D1R1 C3D1R1

CHANNEL C1

PRODUCT UPC1B1C1

SALES RANK 132 156 -

SALES 138.222,09 181.377,26 130.412,60

CHANNEL C1

PRODUCT UPC1B2C1

SALES RANK 36 108 -

SALES 280.826,57 223.492,68 216.759,33

CHANNEL C1

PRODUCT UPC1B4C1

SALES RANK 228 408 -

SALES 47.210,72 40.234,52 60.917,06

1 Page of Data Listed

But this is not a solution in a real system....

My question is, how can I calculate a correct ranking for a metric/variable that use more than one dimension?

Thanks in advance

Pedro

Edited by: Pedro S. Costa on Aug 11, 2009 12:50 PM

AFTER THE EDIT, TEH TEXT OF THE MESSAGE APPEAR ALL OUT OF PLACE!?!?!?

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

1 Answer

  • Posted on Aug 11, 2009 at 11:21 AM

    ....

    Edited by: Pedro S. Costa on Aug 11, 2009 1:21 PM

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.