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

    Shaun Currier

      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:

      Create Sets for Top N and Others | Tableau Software

      ...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
          Shaun Currier

          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
            Tom W

            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.