create monthly ranks

[sabmaldun]

Hello,

I try to create a Rank for Dealers per month. Therefor I have to Loop through all month and create the ranks.

Anyone of you has a Suggestion how to do it? I have written the Code below, but it does not work. I also tried rowno() function, but then it does not start with 1 again for each month.

Thank you guys !!

Set count=1;
For j=2017 to 2018
For i=1 to 12

Ranking:
LOAD DealerID,
$(j) as Year,
$(i) as Month,
sum(YTDPartSales),
$(count) as Rank
Resident YTDTemp
Order By YTDPartSales desc;
Set count=$(count)+1;
Next i;
Next j;

[joseph_morales]

Hi @sabmaldun ,

Try this example file

data_temp:
LOAD * INLINE [
DealerID, Year, Month, YTDPartSales
10, 2018, 11, 250
20, 2018, 11, 430
30, 2018, 11, 320
40, 2018, 11, 280
50, 2018, 11, 356
10, 2018, 11, 65
30, 2018, 11, 25
10, 2018, 12, 350
20, 2018, 12, 387
30, 2018, 12, 398
40, 2018, 12, 260
50, 2018, 12, 401
40, 2018, 12, 156
10, 2019, 1, 253
20, 2019, 1, 189
30, 2019, 1, 350
40, 2019, 1, 421
30, 2019, 1, 61
];

NoConcatenate
Data_1:
load
DealerID,
Year,
Month,
sum(YTDPartSales) as Total_YTDPartSales
Resident data_temp
group by
DealerID,
Year,
Month;

drop table data_temp;

NoConcatenate
Data:
load *,
AutoNumber(DealerID,Year&Month) as Rank
Resident Data_1
Order by Year,Month,Total_YTDPartSales desc;

Drop table Data_1;

Regards

[sabmaldun]

Hey,

thats great. It works perfectly.

Now I have to create quartiles based on the ranks per year, month and group

If rank <=0.1*total rankcount of that month then quartile1,

If rank >=0.1*total rankcount of that month then quartile2

Based on the built Quartiles  I have to calculate the average of those quartiles per year, month and group

Do you know how to do it?

Thank you!