1 2 Previous Next 15 Replies Latest reply on Feb 22, 2018 9:22 AM by Yuriy Fal

Top & Bottom 10 interact with multi-filters

Hello everyone,

I have a question about how to show top and bottom 10 ranks under multiple filters. For example, if we have 200 students I want to see who are the top and bottom 10 boys/ girls based on exam scores of 5 different subjects.

Filter 1: switch between Boy & Girl

Filter 2: 5 Subjects.

Thank you so much!

• 1. Re: Top & Bottom 10 interact with multi-filters

Hi Xihuan,

Lets model your case using Sample Superstore dataset.

Let the [Segment] be your Gender Dimension (boys/girls/pets :-),

[Region] be your Subject Dim, SUM( [Sales] ) be your Scores metric.

Last but not least, the [Customer Name] would be your Students Dim.

A simple view would be a list of Top & Bottom N Customers --

sorted and binned in Top & Bottom by the SUM( [Sales] ) aggregate.

Besides, both [Segment] and [Region] would be used as Filters.

Please find the attached as a basic example (a 'simple' view).

To build the view, the two main points should be learned (from the Help):

-- Creating TopN Sets, and making a Combined Set from the Top & Bottom ones:

https://onlinehelp.tableau.com/current/pro/desktop/en-us/help.html#sortgroup_sets_topn.html

-- Restricting the scope for TopN calculation with the Context Filters applied (first):

https://onlinehelp.tableau.com/current/pro/desktop/en-us/help.html#order_of_operations.html

The latter Help Page (named the Tableau's Order of Operations)

is paramount for making understand how Tableau works under the hood.

And imho, the Tableau Concepts is the single most important Section of the Help.

Back to the topic, the 'simple' view is ... just simple.

It answers the basic question of who are in the Top-Bottom N.

Other questions may arise, and it could happen that

this view doesn't fit well as an answering one.

For example, the 'all-in-one' list (or even a multi-column table)

of the Top-Bottom N Students in each Gender / Subject segment

would be needed. And suddenly it becomes evident that

such a view couldn't be built from the 'simple' one we've got.

Of course, it could, but it would require a different approach from the start.

Nested Sorting / RANK() Table Calculations / Table Calculation Filters -- that's the way:

Fixing "Incorrect" Sorts

I have to stop here, the next turn would be yours :-)

Please reply if you'd have any problems / questions with the above.

Yours,

Yuri

• 2. Re: Top & Bottom 10 interact with multi-filters

Hi YUri,

I have created a sample example in Tableau Public . Should pretty much work with desktop version .

I have created a Rank function first and used it in below Formula

[Rank]<=10 or [Rank]>=MAX({ FIXED [Segment]:COUNTD([Customer Name])}-10)

This will get you top 10 and bottom 10 records

i have used another formula just to highlight top N records.

Hope this gives idea to resolve issue .

• 3. Re: Top & Bottom 10 interact with multi-filters

Hi Arvind,

Thank you both for answering my questions. Arvind I have a question about your response. I want to show the top and bottom 10 customer name based on multiple filters ( Segment and Region). Rank]<=10 or [Rank]>=MAX({ FIXED [Segment]:COUNTD([Customer Name])}-10). This formula fixed Segment only and what about the region? Do we need to fixed all filters?

Thanks

Sally Xihuan Niu

• 4. Re: Top & Bottom 10 interact with multi-filters

Hi Yuri,

Thank you so much for your inspiration. I created a combined set with top 10 and bottom 10 students/companies. Also, I showed my 3 filters. In addition, I created a parameter like you did as #  Top Customers. However, when I select among different filters the chart seems like only show the top portion. Can you please help me with this?

Thank you Yuri

Sally Xihuan

• 5. Re: Top & Bottom 10 interact with multi-filters

Hi Xihuan,

Please recall my second point on Context filters.

You may want to put your Dimension Filters in Context.

Yours,

Yuri

• 6. Re: Top & Bottom 10 interact with multi-filters

Hi Yuri,

Thank you for your quick turnaround. I just add my filters to Context. Can you please quickly explain what does Context do? Also, as you can see my chart still only show top 10 only. I wonder where did my bottom 10 go.

Thank you so much!

Xihuan

• 7. Re: Top & Bottom 10 interact with multi-filters

Hi Xihuan,

I couldn't see the details on your last picture -- it's way too small.

Yours,

Yuri

• 8. Re: Top & Bottom 10 interact with multi-filters

Sorry Yuri,

Please see the picture now. And also, can you please quickly explain what does Context do? Thanks !!

• 9. Re: Top & Bottom 10 interact with multi-filters

Context Filters are change (restrict) the scope

for the FIXED LOD calculations. They applied first.

Please take a look at the Tableau's Order of Operations help page:

Regarding the Top & Bottom 10 (total 20)

you're expecting to observe on a view, please tell:

1) Which Dimension Pill is used for the Top & Bottom Sets

2) If putting this Dim Pill on a Filters shelf

and set it to show Only Relevant values,

how many distinct values would be shown?

Yours,

Yuri

• 10. Re: Top & Bottom 10 interact with multi-filters

Hi Yuri,

Thank you very much for helping.

1). For my case, investment type, Morning* Category are the two Dimension Pills that should have impact on Top & Bottom Sets.

2). My two Dimension Pills are under only showing Relevant Value mode. I want to see top and bottom distinct values.

I feel like I'm getting to where I want but not exact. Please see the picture. I have top 10 but only bottom 9 show up.

For this case, I have only bottom 5 shows up.

And I'm pretty sure there should be at least 10 negative numbers. That confused me a lot.

I cannot thank you enough Yuri.

Best regards,

Sally

• 11. Re: Top & Bottom 10 interact with multi-filters

Hi Xihuan,

So I understand that you're filtering

Top & Bottom [Branding Name] values.

What if you'd remove the Aggregate Filter?

Those Green Pill called SUM(Estimated Net...)?

Would you observe 20 Marks then?

I assume that you have at least 20 unique values

of the [Branding Name] in your data (after Context).

Yours,

Yuri

• 12. Re: Top & Bottom 10 interact with multi-filters

HI XIhuan,

I have updated the VIz a little bit

The Formula is changed to Fixed Region and Segment .

[Rank]<=[Top N selection] or [Rank]>=MAX({ FIXED Region,[Segment]:COUNTD([Customer Name])}-[Top N selection])

I have also added Filters like you would have for Region and Segment which would not impact the Viz for any combination of Selection.

The above LOD calculation keeps data in context with Rank applied overall .

CHeck the Rank in Row selection.

Table calculation suggest calculation across specific dimensions arranged in order Region , Segment and Customer where Rank should restart at Segment Level.

Tableau Public Link with updated Viz:

https://public.tableau.com/views/TopNandBottomN_4/topNBottomN?:embed=y&:display_count=yes&publish=yes

• 13. Re: Top & Bottom 10 interact with multi-filters

Hi Yuri,

It start working! Thank you so much for your help and your precious time.

I guess my issue was the sum filter and I didn't fix my filter as Context. These two things make a big difference.

Thank you my friend!

Xihuan

• 14. Re: Top & Bottom 10 interact with multi-filters

Thank you Arvind for your help!

Best regards,

Xihuan

1 2 Previous Next