4 Replies Latest reply on Jan 11, 2017 8:56 AM by Matthew Risley

    Sorting Behavior

    Matthew Risley

      Hey, Community.

       

      Attached workbook is 9.3.

       

      I have a requirement to sort the top 3 within a category. Easy. Following this sort of method to achieve result as I've done before: Finding the Top N Within a Category | Tableau Software

      However, the sorting behavior is off. Now I knew about this issue beforehand and it was discussed here: Re: Sorting Behavior  But I haven't had this issue in a long time. Is this something that has been solved? I'm using 9.3 so is it a version issue?

       

      Look at the incorrect sort:

       

      Sorted this way:

       

      This frustrates our users to no end. It's been an age since I've done sorting like this and I must be missing something simple... What am I doing wrong?

        • 1. Re: Sorting Behavior
          Jamieson Christian

          Matthew,

           

          Use RANK() instead of INDEX(). The latter is really not meant for sorting.

           

          [Rank]

          RANK( SUM([Number of Records]) )

           

          Then replace [index] with [Rank] on your Rows shelf and configure the table calc the same way that [index] was configured.

          • 2. Re: Sorting Behavior
            Matthew Risley

            Jamieson,

             

            This gives me the output I need. Forgot about rank! Great solution!

             

            Still interesting to see why the sort box isn't giving me desired output- with or without INDEX().

            Also kind of confusing to see that the KB article isn't using rank as well.

            • 3. Re: Sorting Behavior
              Jamieson Christian

              Matthew,

               

              That KB article is very old and has a disclaimer at the top that it's no longer actively maintained. It may even predate the introduction of the RANK() functions. (The screenshot shows a very old version of the calculation editor.)

               

              When you do a simple sort on [Type of Guy], the reason you get odd results is that Tableau is sorting [Type of Guy] the same way for all [Business] segments. (Presumably it's basing the sort off the TOTAL across all [Business] segments, at that point.) There are advantages to this behavior in some cases, but it's not always what you want. That's where RANK() becomes useful; it allows you to sort [Type of Guy] differently within each [Business] segment.

              • 4. Re: Sorting Behavior
                Matthew Risley

                Everything you said is true! Still a bummer it wasn't "updated" yet.

                 

                I'm also a dummy and forgot about this video: Fixing "Incorrect" Sorts with INDEX()

                It also solved my issue, but RANK still takes the cake here.

                 

                I should've searched a few minutes longer.

                 

                Thanks for your help and your activity on the forums!

                 

                Best,

                --Matt