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

Multiple Top N Filter by Class

MichaelH H Nov 4, 2016 2:44 PM

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

ABCD.twbx 94.2 KB

1. Re: Multiple Top N Filter by Class

D Barnetson Nov 4, 2016 11:57 AM (in response to MichaelH H)
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)

Rank by Class.twbx 18.6 KB

1 of 1 people found this helpful
Like Show 1 Likes(1)

Actions
2. Re: Multiple Top N Filter by Class

Shinichiro Murakami Nov 4, 2016 12:00 PM (in response to MichaelH H)
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

multiple Rank_SM_9.2.twbx 17.0 KB

1 of 1 people found this helpful
Like Show 1 Likes(1)

Actions
3. Re: Multiple Top N Filter by Class

MichaelH H Nov 4, 2016 1:05 PM (in response to Shinichiro Murakami)

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
Like Show 1 Likes(1)

Actions
4. Re: Multiple Top N Filter by Class

MichaelH H Nov 4, 2016 1:12 PM (in response to D Barnetson)

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
Like Show 1 Likes(1)

Actions
5. Re: Multiple Top N Filter by Class

Shinichiro Murakami Nov 4, 2016 1:40 PM (in response to MichaelH H)

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
Like Show 2 Likes(2)

Actions
6. Re: Multiple Top N Filter by Class

MichaelH H Nov 4, 2016 2:47 PM (in response to Shinichiro Murakami)

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.

Thanks in advance for your help,
Michael
1 of 1 people found this helpful
Like Show 1 Likes(1)

Actions
7. Re: Multiple Top N Filter by Class

Shinichiro Murakami Nov 4, 2016 3:10 PM (in response to MichaelH H)
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

ABCD_SM_9.3.twbx 120.4 KB

2 of 2 people found this helpful
Like Show 2 Likes(2)

Actions
8. Re: Multiple Top N Filter by Class

MichaelH H Nov 4, 2016 3:31 PM (in response to Shinichiro Murakami)

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
Like Show 1 Likes(1)

Actions
9. Re: Multiple Top N Filter by Class

MichaelH H Nov 4, 2016 11:13 PM (in response to Shinichiro Murakami)

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
Like Show 0 Likes(0)

Actions
10. Re: Multiple Top N Filter by Class

MichaelH H Nov 5, 2016 6:14 AM (in response to Shinichiro Murakami)

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
Like Show 0 Likes(0)

Actions
11. Re: Multiple Top N Filter by Class

Shinichiro Murakami Nov 5, 2016 6:37 AM (in response to MichaelH H)

change all the filters to context filter,
if the size is not huge, yes.

Thanks,
Shin
1 of 1 people found this helpful
Like Show 1 Likes(1)

Actions