Skip to Content
0
Aug 11, 2009 at 10:46 AM

PAS - Calculating Ranking

33 Views

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!?!?!?