Select top 10 by category

[Nithin_Prakash]

Hi there,

I have a dataset with columns Area, District and Sales. I need to get top 10 Area by Sales for all the N Districts. I could solve this using partition in SQL but this data is got by joining 2 tables and I need to set it up through Alteryx.

Thanks in advance

[JoshuaGostick · Accepted solution]

Hi @Nithin_Prakash,

Try using the Sort tool to sort on each Area and District with Sales in descending order. Then, add a Sample tool, select 1 of every N rows, add 10 for N=, and group on Area and District.

Let me know if this works of if you have any questions!

Thanks,

Josh

[Nazik_Ghukasyan]

@JoshuaGostick hello

I have a database with following columns

• Product
• Year of sales (2010-2020)
• Price

can you help me to understand how I can filter on top 20 products per sales for a year 2017-2020?

thank you

Nazik

[trevorwightman]

For this I would use a combination of the sort, tile and filter tool.

1. Filter your data to 2017-2020

2. Sort by price descending and product such that you have each product grouped together and sorted descending.

3. Use the tile tool to create a tile sequence number (I believe you can check the unique tile option)

4. Filter any tile sequence number less than or equal to 20.

Let me know if this works!

[Nazik_Ghukasyan]

perfect, thank you so much

it is working well and with your help I learnt one more tool which is TILE tool 🙂

[ht9850]

Here's my answer to select top 10 by category

[MWilkinson]

I know this was answered months ago, but for people coming across this thread--

You can also use the sample tool, and in this case you would select "First N rows" and then make N = 20.

You would group by what you want to actually take 20 of, "District" in this example.