5 Replies Latest reply on Oct 31, 2013 7:38 AM by Matt Lutton

    Sorting with Sets & Ranks

    Brianne Hooker

      I am creating a chart based on educational data.  I want to show the top five campuses per degree level (by head count) and the top 5 courses per campus (also by head count).  I have created 2 sets.  The first set is degree level and campus.  The other set is degree level, campus, and course.  I ranked both of these sets according to a head count of students and then filtered them to show only the top 5. The calculations appear to be working correctly; the top 5 for both campuses and courses are as I would expect them to be and when I apply the ranks to the label chart, the ranks are correct.  I then sorted by course rank so that the courses, within each campus, are sorted in descending order by head count.  The problem is when I sort by campus rank, only the Bachelors set sorts correctly.  The Masters does not.  I can't figure out why one half of the set would sort correctly, but the other would not!  I could easily get around this by manually sorting, but I'm concerned that there is a bigger issue causing this.

       

      Any help?!

       

      Thanks!

        • 1. Re: Sorting with Sets & Ranks
          Shawn Wallwork

          Hi Brianne, I don't have the time to really understand what your issue is right now (sorry) but now that it is 20+ days old I fear it might get lost, so I'm bumping it.

           

          --Shawn

          • 2. Re: Sorting with Sets & Ranks
            Jonathan Drummey

            Hi Brianne,

             

            When you do a sort on a dimension pill or a set, you are sorting across the entire data set, after any row-level filtering on dimensions but before any filters on aggregates. You can see this in your workbook because Phoenix and SoCal change places when you remove CNT(Number of Records) from the view. I've also set up an example of this with 12 rows of data in the attached sorting.twbx workbook. So that describes one issue, but it doesn't solve it nor solves the issue with Houston and SD Main Campus in the Masters data, and there's no way within sorting of Sets to "restart" the sort the way you'd like.

             

            However, when we set up a table calculation, Tableau creates a "set" of everything in the Compute Using and sorts that, and then we can restart and partition that as we want based on the Compute Using, Restarting Every, and At the Level, which is exactly what you did with your Rank calculations. So instead of trying to get a sort that matches the Rank, just use a discrete version of the Rank calc to get the data in the right order and Tableau will sort that alphanumerically (so if you want a descending sort then just use a calc like -[Rank]). I set that up in the "use Rank for Sort" in the attached. With this, we no longer need to do any sorting in the view, the table calcs take care of it.

             

            Even though we can set the same table calc to have different compute usings in the same view, for ease of maintenance, I'd suggest using two separate rank calcs, that way it's really clear which pill someone is manipulating.

             

            Jonathan

            • 3. Re: Sorting with Sets & Ranks
              Jonathan Drummey

              Scratch what I wrote about the sort order, I think what I'm seeing is a bug. I went to reopen the workbook and this is the sort order I saw, which would indicate that all filtering is happening before the sort:

               

              screenshot1.gif

              Moving the SUM(Value) filter around doesn't change anything. However, after removing the pill from the Filters Shelf and re-adding it, I can bring back the behavior I was describing:

               

              screenshot2.gif

               

              I'll submit this to Tableau tech support and see what they say.

               

              Jonathan

              • 4. Re: Sorting with Sets & Ranks
                Marie Austenaa

                Hi,

                 

                did you get a reponse from the Tech Tableau team?

                 

                I am having troubles filtering the data and then ranking the top 10 values.

                 

                Thanks

                 

                Marie

                • 5. Re: Sorting with Sets & Ranks
                  Matt Lutton

                  Marie:

                   

                  I would suggest opening a new discussion/question on the Forum, along with a packaged workbook (.twbx) file demonstrating your setup, as well as a mock up of what you want as an expected result.  Someone will be happy to assist you.  Cheers.