2 Replies Latest reply on Jun 29, 2016 6:00 AM by Tom W

# Top N on filtered data - sets don't seem to work

Hello, I have some data similar to this:

Part Number  |     Part Name     |     Somefield1     |     Somefield2       |     Date

123456          |     Part Name A  |     blah               |     blah                 |     somedate

123456          |     Part Name A  |     blah               |     blah                 |     somedate

888888          |    Part Name A  |     blah               |     blah                 |     somedate

888888          |     Part Name A  |     blah               |     blah                 |     somedate

444555          |     Part Name B  |     blah               |     blah                 |     somedate

666444          |     Part Name B |     blah               |     blah                 |     somedate

222222          |     Part Name C  |     blah               |     blah                 |     somedate

111111           |     Part Name C  |     blah               |     blah                 |     somedate

777777          |     Part Name D |     blah               |     blah                 |     somedate

999999          |     Part Name E  |     blah               |     blah                 |     somedate

999999          |     Part Name E  |     blah               |     blah                 |     somedate

999999          |     Part Name E  |     blah               |     blah                 |     somedate

One note about the demographics of the data.  As one would expect, part number to part name is a many-to-1 relationship.  In other words, a given part number implies a single name, but a part name doesn't imply a single part number.

I'd like to make a line plot with the following characteristics.

Columns: dates from the "Date" column

Rows: count of distinct part numbers

Color: part names from the "Part Name" column

Mark type: line

This should give me one colored line per part name.  Trouble is, there are many distinct part names and I only want to show the top 5 or so to keep it from being confusing.  The remaining part names (call them the "Bottom M") I want to all be lumped together into a grey colored "other" line whose value is the count of rows from the non-Top N part names.

I tried doing this using sets in Tableau as directed here:

...but I ran into an issue.  Sets don't seem to be subject to filters.  So when, for example, I filter by Somefield1 and Somefield2, the set's members are not the top N of data within the scope of those filters, it's still just the Top N for the scope of all the data.

Am I understanding sets correctly?  Is there a way to get around the set scope issue?

• ###### 1. Re: Top N on filtered data - sets don't seem to work

This link seems to describe the issue: Unexpected Results when using Top N Filter with Other Filter | Tableau Software

It sums it up at the bottom: "The Top N sort is calculated before looking at the field on the Filters shelf"

It suggests three options to get around this.  Option 1 won't work for me because I cannot add the filter to context.  Option 3 won't work for me because my filters must be dynamic with the data, so I cannot filter using parameters in calculated fields.

That leaves option 2.  And, yes, I can successfully filter the view using a table calculation.  (Side note: I actually used rank_unique(), not index(), since this is more appropriate for me.) But simply filtering the view in this way is different from doing coloring where the legend values are either a Top N Part Name or "Other."  To do this coloring I think you must create a calculation from the table calc like this:

if (RANK_UNIQUE(COUNTD([Part No]), 'desc') < 5)

then [Part Name]

else "Other"

END

...and then use this field to color.  But this is not a valid calculation because it mixes aggregate and non-aggregate values.

I'm stuck!

• ###### 2. Re: Top N on filtered data - sets don't seem to work

Hi Shaun,

It would help a lot if you prepare and attach a Tableau Packaged Workbook to replicate your scenario. I'd also recommend taking a read through the 'Suggested Reading' on the forum homepage as there's some good advice for getting started.