11 Replies Latest reply on Nov 5, 2016 6:37 AM by Shinichiro Murakami

# Multiple Top N Filter by Class

Dear Tableau Community,

Please help me to find a way on how to create multiple filters, suppose I have different class of items and for each class I need to filter their top values (each different). I'm attaching the excel file for reference.

Thanks a lot,

Michael

• ###### 1. Re: Multiple Top N Filter by Class

Hi Michael,

Here's what you can do:

Create a calculated field called 'Rank': (this will rank 'Cost' by the SUM of 'Cost')

Then another called 'Rank Filter': (this will filter your 'Rank' by your 'Class')

Place the pills as per the picture below: (set 'Rank Filter' to 'True')

Now, set 'Class' to 'Show Filter', then select from the drop down arrow on the filter click 'Customize' and uncheck 'Show 'All' Value'.

...and there you have it

etc...

Hope this helps.

Don

I've attached the working version (Tableau v10)

1 of 1 people found this helpful
• ###### 2. Re: Multiple Top N Filter by Class

Hi  Michael,

Here is my approach.

[Rank in Class]

rank_unique(sum([Cost]),'asc')

[Filter]

if (attr([Class])="A" and [Rank in Class]<=[Top N for A])

or

(attr([Class])="B" and [Rank in Class]<=[Top N for B])

or

(attr([Class])="C" and [Rank in Class]<=[Top N for C])

or

(attr([Class])="D" and [Rank in Class]<=[Top N for D])

then "Show" else "hide"

end

Thanks,

Shin

1 of 1 people found this helpful
• ###### 3. Re: Multiple Top N Filter by Class

Dear Shin,

Again thanks a lot for helping me out. I'm having some problem in filtering, sorry I'm just starting to learn tableau.

The problem is I'm not getting the "Show" option when placing the Filter calculation on the filter, I'm getting only "Hide"

I'm getting my Top N base on a calculating fields as well, please see the attached. Sorry again to bother you.

Thanks,

Michael

1 of 1 people found this helpful
• ###### 4. Re: Multiple Top N Filter by Class

Dear D Barneston,

Thanks also for your help, I am asking Shin's approach as we work in the same field.

Thanks again,

Michael

1 of 1 people found this helpful
• ###### 5. Re: Multiple Top N Filter by Class

Are you able to attach packaged workbook?

Fist thing I thought of is checking "Compute using"

Thanks,

Shin

2 of 2 people found this helpful
• ###### 6. Re: Multiple Top N Filter by Class

Dear Shin,

I attached the packaged workbook on the original post, please assist. My Top N should be the Daily, it should be link to that so that when the number of items increase or decrease my Top N will also change.

Michael

1 of 1 people found this helpful
• ###### 7. Re: Multiple Top N Filter by Class

Need to modify your calculated fields.

[Yearly (SM)]

{fixed [ABCD Class]:AVG([Frequency])} * {fixed [ABCD Class]:COUNTD([Item])}

[Daily (SM)]

[Yearly (SM)]/[Workday]

[Rank]    // Not sure this is asc or desc

rank_unique(sum([Cost]),'desc')

[Filter]

if [Rank]<= attr([Daily (SM)]) then "Show" else "hide" end

Thanks,

Shin

2 of 2 people found this helpful
• ###### 8. Re: Multiple Top N Filter by Class

Thank you very much Shin, I will let you know, I'll try this later it's 3am now.

Thanks again,

Michael

2 of 2 people found this helpful
• ###### 9. Re: Multiple Top N Filter by Class

Hi Shin,

Good morning!

I use the your formula but when I use the option "Compute Using" and I choose Item. Filter is not working but I when I compute using "Table Down" instead of Item I'm getting only the first top on the list i.e. Class A and Class B C and D are all hidden.

I'll try to send you my file but it's quite big.

Thanks again,

Michael

• ###### 10. Re: Multiple Top N Filter by Class

Hi Shin,

Prior to my email, the filter is now working when I use the fixed formula instead of include, I chose the option from compute using from the level of "Item" and restarting every "Class".

But the problem is after I change my formula from include to fixed its not showing the correct value, is it possible to send you the original package workbook via personal email?

Thanks again,

Michael

• ###### 11. Re: Multiple Top N Filter by Class

change all the filters to context filter,

if the size is not huge, yes.

Thanks,

Shin

1 of 1 people found this helpful