How do I calculate tertiles based on revenue and group?

[t_guet01]

Hey guys,

I am currently trying to assign customers to tertiles. Say, I have the following data given:

Customer	Type	Revenue
ABC	B2B	10
DEF	B2B	20
GHI	B2B	15
JKL	B2C	50
MNO	B2C	10
PQR	B2C	5

I already aggregated this table as a temporary one from the customer data and the transactions to calculate the annual revenue.

I would now like to assign the customers to tertiles within their type (group). This is the desired outcome:

Customer	Type	Revenue	Tertile
ABC	B2B	10	1
DEF	B2B	20	3
GHI	B2B	15	2
JKL	B2C	50	3
MNO	B2C	10	2
PQR	B2C	5	1

As you can see, I rank my customers within their specific group (here whether they are a B2B or a B2C customer) based on their annual revenue. I tried using rank and fract but I couldn't get it runnig.

Any ideas?

Thanks!

[Aditya_Chitale]

Have you tried :

Aggr(Rank(-Revenue),Type, Customer) 

Make sure to add  Revenue as measure and not dimension.

Regards,

Aditya

[Ahidhar]

you can do this in script like this

tab:
load *,window(recno(),Type,'ASC',Revenue) as Tertile;
load * inline
[
Customer, Type, Revenue
ABC, B2B, 10
DEF, B2B, 20
GHI, B2B, 15
JKL, B2C, 50
MNO, B2C, 10
PQR, B2C, 5
];