Top n per record

[Nouha]

Hello, 

I have a dataset that resembles the following , with over 10 million rows

Weeks	Products	Sales
Week 1	A	61
Week 2	B	6
Week 3	D	189
Week 4	N	522
Week 1	J	9
Week 2	R	28
Week 3	Z	3
Week 4	T	923
Week 1	A	9
Week 2	B	2
Week 3	DF	956
Week 4	E	39
Week 1	H	6466
Week 2	S	89
Week 3	D	9
Week 4	GF	1
Week 1	F	28
Week 2	Z	69
Week 3	D	379
Week 4	D	356

my aim is to have for each week, the top N products of each week displayed so :

Weeks	Products	Rank	Sales
Week 1	A	1	5266
Week 1	B	2	854
Week 1	D	3	89
Week 1	N	4	63
Week 2	J	1	896
Week 2	R	2	75
Week 2	Z	3	62
Week 2	T	4	34
Week 3	A	1	7569
Week 3	B	2	6542
Week 3	DF	3	352
Week 3	E	4	24
Week 4	H	1	9742
Week 4	S	2	352
Week 4	D	3	24
Week 4	GF	4	4

any idea how about how to achieve that ?

Thank you

[BenMoss]

First of all sort your data by sales descending.

Then to create a rank field we can use the multi Row formula tool, use this to create a new field and call this RANK.

Then use the statement [row-1:RANK]+1

There is an option to group by, in this option make sure you check the week field, this means the count will reset for every week.

Final step is to use the sample tool, choose the first N and set the number to what you like.

Again, set the group by criteria to be your week field, which means you will get the Top N records for each week.

Ben