Calling all Racers for the Alteryx Grand Prix! It's time to rev your engines and race to the stage at Inspire! Sign up here.

Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer and Intelligence Suite.

Top n per record

6 - Meteoroid



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


Weeks    ProductsSales
Week 1A61
Week 2B6
Week 3D189
Week 4N522
Week 1J9
Week 2R28
Week 3Z3
Week 4T923
Week 1A9
Week 2B2
Week 3DF956
Week 4E39
Week 1H6466
Week 2S89
Week 3D9
Week 4GF1
Week 1F28
Week 2Z69
Week 3D379
Week 4D356


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


Weeks    ProductsRankSales
Week 1A15266
Week 1B2854
Week 1D389
Week 1N463
Week 2J1896
Week 2R275
Week 2Z362
Week 2T434
Week 3A17569
Week 3B26542
Week 3DF3352
Week 3E424
Week 4H19742
Week 4S2352
Week 4D324
Week 4GF44



any idea how about how to achieve that ?


Thank you


ACE Emeritus
ACE Emeritus
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.