7 Replies Latest reply on Dec 5, 2018 12:11 PM by Stephen Beste

    Sort and rank on multiple text table columns

    Stephen Beste

      I'm an experienced computer guy, but I'm new to Tableau (Tableau Public). I'm stymied trying to get a text table to sort as I want. Can someone give me a steer?

      What I have:

      As is.jpg

      Purpose: I'm showing the sales of various makes & models of aircraft by year, sorted by sales in the most recent year. Hence, 15, 14, 12, 12, ...

      • I manually set the sort order on the most recent year every time I update the data.
      • The [Year] dimension is discrete integer years. I have a Top filter on it in the sheet so it just shows the top 4 values of INT([Year]). The data goes back 15 years or so. This works fine.

      Problems: I want to

      1. Automatically do a primary sort on the most recent Year column. I don't want to have to remember to manually update it every time the data changes or a year gets added.
      2. Do a 2nd-level sort on [Make & Model]. Notice how the last 3 rows in the image above are NOT sorted this way.
      3. Show a Rank column based on the values for the most recent year.

      Desired outcome:

      Desired.jpg

      Any advice? I'm using Tableau Public so I can't open any workbooks. Sorry. I do have complete control over the upstream database, if that's where this has to get calculated.

      Thanks,

      Steve

        • 1. Re: Sort and rank on multiple text table columns
          Shinichiro Murakami

          Hope this helps.

           

          Using superstore example.

           

           

          Under same Rank, the name sorting is set alphabetical as default, and you don't need to do anything I think.

           

           

          Thanks,

          Shin 

          • 2. Re: Sort and rank on multiple text table columns
            Stephen Beste

            Shin,

            Thank you for your quick reply! You've taken me part way to a solution. Here's what I found.

             

            1. I can get the calculated field for Qty Most Recent Year to work. And by matching on YEAR([As Of Dt]) - which is a field in every row of my joined data - I'm able to make it dynamic as new years are added. Great!

            I know it works, because when I temporarily add it to the viz, I see that it has the correct values:

            2. The RANK_DENSE() function, not so good. It keeps calculating rank horizontally, as rank within the 3 years, not vertically. Indeed, it even tells me that it's calculating "across".

            I went into Table Calculations and set it to compute using Table (down), but the result is nowhere near what I'm after.

            At this point, i'm just whacking at things uselessly, the Sorcerer's Apprentice. I need to understand more deeply. I think it has to do with how to use RANK() when there are more than one column, but I can find no examples of this on the net.

            Any ideas?

            Thanks,

            Steve

            • 3. Re: Sort and rank on multiple text table columns
              Shinichiro Murakami

              if you could attach sample mocked up data as twbx, the support becomes much much easier?

               

              Shin

              • 4. Re: Sort and rank on multiple text table columns
                Stephen Beste

                Shin,

                 

                Of course. Here's the twbx downloaded from Tableau Public. The sheet I'm working in is called "Leader Board (2)". Let me explain the data. It consists of:

                1. Our Aircraft. 4,678 rows, one row for every individual aircraft in the U.S. Federal Aviation Administration's current aircraft registry, of a make and model that interests me. The key field is [N-Number], the number assigned to each aircraft.
                2. Inventory By Year. 33,578 rows, one row for every individual aircraft + a year in which it existed, through 2018. Thus, if an aircraft was manufactured in 2016, it will have 3 rows in this table - 2016, 2017, and 2018. Thus, by summing up the joined tables where [Year] = say, 2017, I can get the total number of aircraft in the fleet in 2017. Counting for 2018 yields the total number in 2018. The difference from 2017 to 2018 yields the number added to the fleet in 2018. These figures are the substance of the  "Leader Board" sheet.

                 

                But that's all background. The heart of the problem is how to get the RANK() functions to work on just one of the [Year] columns when more than one Year column is showing in the viz. In my case,  I want rank in the most recent year, which is currently 2018.

                 

                I can get the result I want if I restrict the viz to just 2018. (Or, rather, since Tableau needs two years to calculate a difference, I filter to 2017 and 2018 and then hide the 2017 column.) I then set the table calculation to Down. The result:

                This is with:

                and

                But, again, this would be more useful if it showed three years. You could then see how the top makes & models are jockeying for market-share position over time. That's where I'm trying to go.

                 

                Shin, I REALLY appreciate your willingness to mess with this.

                Steve

                • 5. Re: Sort and rank on multiple text table columns
                  Shinichiro Murakami

                  several steps

                   

                   

                   

                   

                  Filters and Level of Detail Expressions - Tableau

                   

                   

                   

                   

                   

                  Expand Rank across year

                   

                   

                   

                   

                   

                  If this works, please mark my answer as correct / helped, not from inbox but from original post.

                   

                  Thanks,

                  Shin

                  • 6. Re: Sort and rank on multiple text table columns
                    Stephen Beste

                    Shin,

                     

                    YES! IT WORKS! Thank you, thank you! I appreciate the time you put into this and the clarity of your answers. Pictures and the twbx file help so much. For the record, in case anyone else comes this way, here's a summary of the solution you gave me in your twbx. It's a little simpler than your last post suggests.

                     

                    1. Create these calculated fields that build on each other. Change the names to suit yourself. All are invisible except the last one, "Rank", which appears as a column head in the table.

                    2. Make Rank into a discrete field.

                    3. Drag it to the Row shelf as shown here:

                    4. Set the table calculations on the Rank pill where it sits on the Row shelf. Do as Shin said:

                    Also, using the drop-down pick list opposite "Rank" in the above panel, set:

                    5. Set the Make & Model sort to alphabetical within ranks.  It apparently does not sort automatically.

                    As you see from the screen shot above, Shin gave me just what I wanted.

                    Many thanks,

                    Steve

                    • 7. Re: Sort and rank on multiple text table columns
                      Stephen Beste

                      Shin's replies opened the door and got the result that I needed. He's tenacious, brilliant, and clear. Case closed with many thanks.

                      Steve Beste

                      1 of 1 people found this helpful