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