3 Replies Latest reply on Jan 27, 2017 10:18 AM by Santiago Sanchez

# How to Filter Top-N that actions multiple fields?

Hello,

I have been attempting to filter top 5 categories based on a parameter, which then filter the sub-categories of that category to its top 5 based on the parameter. I know that really doesn't make sense, so for example,

I have 10 categories:

Shop

Lawn

Hardware

Automotive

Electronics

Furniture

Lighting

Impulse

Home Décor

Seasonal

Within each of those categories they're a series of sub-categories. For example, the category Shop has 15 sub-categories (safety, apparel ect.)

I have categories filtered to top 5 based on [parameter], but how do I get the categories, sub-categories to filter to its top 5 based on [parameter] within the category itself. I've attempted to use the index() function in a calculated field to rank the sub-categories 1-15, but cant seem to get it to filter correctly to have it adjacent to the category and parameter.

Any suggestions would be helpful and welcomed. If you are still confused with the ask I can provide a sample workbook for clarity.

Thanks,

Dustin

• ###### 1. Re: How to Filter Top-N that actions multiple fields?

Hi Dustin,

Attached is an example using the RANK() function. It's similar to INDEX() in that both are table calculations and so you can specify the scope in which they run. I suspect on your workbook the scope is what's causing the undesired behavior.

The calculation you can look at is called [Rank - SubCategory] and is defined as ([Top N:] is a parameter):

RANK_UNIQUE(SUM([Sales]), 'asc') <= [Top N:]

You can drag the calculation to filter and specify the scope here:

Once there, you can set it to run within each category like this:

That should do it!

• ###### 2. Re: How to Filter Top-N that actions multiple fields?

Hello Santiago,

Thank you for your reply and suggestion, I think this should do the trick. However, I am running Tableau 9.3 and it will not allow me to open your provided example. If it is not too much trouble could you repost with a version for 9.3.

Thanks again!

Dustin

• ###### 3. Re: How to Filter Top-N that actions multiple fields?

Dustin, here it goes in version 9.3.