7 Replies Latest reply on Oct 30, 2017 1:48 PM by Nandhakumar Ramanathan

    Nested Sorting without Combined field

    kazuya toida

      Hello.

       

      My English is not good but hope you understand me.


      I want to create a view like attachment,but don't want to use Combine Fields.


      Any idea?



        • 1. Re: Nested Sorting without Combined field
          Prashant Sharma

          Why you do not want to use combined field? Are you using hierarchies? I do not think that this is possible without combined fields.

           

          Warm Regards,

          Prashant Sharma - India | LinkedIn

          • 2. Re: Nested Sorting without Combined field
            Jonathan Drummey

            An INDEX with an Advanced Compute using on the sorting dimension(s) and a chosen sort measure will do the job without a combined field. The Advanced Compute Using internally creates a "combined field" that is then sorted by the chosen measure. That INDEX calc would then be placed as a discrete measure onto the Rows Shelf.

             

            Jonathan

            3 of 3 people found this helpful
            • 3. Re: Nested Sorting without Combined field
              Prashant Sharma

              Thanks Jonathan. I haven't thought that way.

              I am trying what you have suggested & got the correct answer.

              So, sharing the steps -

              1) Create a calculated field using Index()

              2) Right click on this index function calculated field & make it discrete

              3) Put this field in between the two dimensions.

              4) Go to advanced option of this function

              5) Put the field like below -

              Advanced Section.JPG

              Here First put the dimension which you are using at the lowest level like I have used continents first.

              Also at the bottom section use Sales with sum in descending order.

              6) You can choose At the level as "Container" & Restarting at "Continent" (But This will not affect the view)

              At the level.JPG

              I think process is correct. Am I right Jonathan?

               

              Warm Regards,

              Prashant Sharma - India | LinkedIn

              1 of 1 people found this helpful
              • 4. Re: Nested Sorting without Combined field
                Jonathan Drummey

                Hi Prashant,

                 

                Thanks for doing this! The instructions are essentially there, here's a quick rewrite to include details for people who aren't familiar with table calcs. I also took out step #6 as the settings are unecessary and if implemented the wrong way (which I've seen to be pretty common with new users who are still getting used to navigating around these not-very-user-friendly dialogs) will lead to incorrect results and/or frustrations.

                 

                1) Create a calculated field using the formula INDEX() and save it.

                2) In the Measures window, right click on this Index calculated field & choose "Convert to discrete."

                3) Drag the Index field on to the Rows Shelf between the two dimensions. Tableau adds the Index to the view.

                4) Right-click on the Index pill you just added and choose Edit Table Calculation... The Table Calculation dialog appears.

                5) In the Compute Using drop-down, choose the Advanced... option. The advanced dialog appears.

                6) Use the >,<,Up, and Down buttons to move the dimensions from Partitioning into Addressing in the exact order that you have them on the Rows Shelf so the left-right order in rows is reflected in a top-bottom order in the Addressing window, in the case of your original screenshot it would be Region, Department.

                7. In the Sort area, click the Field button to set up a custom sort on Sales/Sum/Descending. This will sort the combinations of distinct value(s) of the addressing dimensions by the SUM(Sales) measure, duplicating the effect of the combined field.

                8. Click OK to close the Advanced dialog.

                9. Click OK to close the Table Calculation dialog.

                10. Click on the Index pill on Rows and uncheck Show Header to hide the Index column.

                 

                Here's a pic of the results:

                 

                2014-07-24 07_56_59-Tableau - Nested Sorting using Index.png

                 

                Note that the Index results you'll see aren't 1,2,3,... for each region because we've set up the calc only have one partition (the entire data set), however because Region is in the view the values of index are still in order. If you want to see a 1,2,3, for each Index then in the Table Calculation dialog you can set Restarting Every to Region:

                 

                2014-07-24 07_59_04-Tableau - Nested Sorting using Index.png

                 

                Also, I'm really used to first thinking of INDEX() because that's what was available when I started using Tableau. INDEX() doesn't support ties, though James Baker and Richard Leeke did some really nice work to make calcs that would do ranking & percentiles & support ties. Tableau 8.1 introduced several RANK functions that do support ties, and the RANK() function takes a measure as an argument  so the easier option that I should have suggested first is:

                 

                1) Create a calculated field using the formula RANK(SUM([Sales])) and save it.

                2) In the Measures window, right click on this Rank calculated field & choose "Convert to discrete."

                3) Drag the Rank field on to the Rows Shelf between the two dimensions. Tableau adds the Rank to the view. The default Compute Using of the table calculation is Table (Down), which means that the view is automatically addressing on both Region & Department, and the default setting of RANK is to do a descending sort of the given measure, so it's already duplicating the Advanced Compute Using & custom sort of the Index instructions above. Here's the view:

                2014-07-24 08_04_25-Tableau - Nested Sorting using Index.png

                4) Click on the Rank pill on Rows and uncheck Show Header to hide the Rank column.

                 

                The RANK() function is a competition rank, if you want to use a different kind of ranking then you can use another one of the functions. If you want the rank to be 1,2,3... for each Region then you can just set the Compute Using to Department using the right-click context menu on the pill.

                 

                I attached a Tableau 8.2 workbook showing the options.

                 

                Jonathan

                6 of 6 people found this helpful
                • 5. Re: Nested Sorting without Combined field
                  Satheesh Kumar Kuna

                  Perfect !! Thank you Jonathan !!

                  1 of 1 people found this helpful
                  • 6. Re: Nested Sorting without Combined field
                    Nandhakumar Ramanathan

                    Hi Jonathan,

                     

                    How can I use this scenario if i have one dimension and multiple measures?

                     

                    Thanks & Regards,

                    Nandha

                    • 7. Re: Nested Sorting without Combined field
                      Nandhakumar Ramanathan

                      Hi Jonathan,

                       

                      Please let me know how can i use this logic in this scenario?

                       

                      Want to get Top and Bottom N values in all measure columns.

                       

                      Thanks & Regards,

                      Nandha