9 Replies Latest reply on Jan 5, 2017 2:01 PM by mark.welsh

    Educational Brain Teaser: One-click sort for a table calculation

    Jonathan Drummey

      Chris Love has started a "Tableau: Good to Great" series at the Information Lab, his most recent tip is on Sorting on Table Calculations at http://www.theinformationlab.co.uk/2015/06/10/tableau-good-to-great-sorting-on-table-calculations/. The goal is to have a view that shows the Percent of Total Sales for each Category & Region, filtered for each Region, and sorted on the % of Total. Here's a view of the result built using the steps from the video:

       

      2015-06-15 18_09_14-Tableau - sorting a table calculation using LOD final.png

       

      Here's the same resulting view built showing one key difference: The One-click sort is available.

       

      2015-06-15 18_02_28-Tableau - sorting a table calculation.png

      For the brain teaser, how would you build this view?

       

      As usual, in your answer below give this a rating from 1 (easy) to 5 (extremely difficult or impossible).

       

      [edited 20150615 to add a packaged workbook]

        • 1. Re: Educational Brain Teaser: One-click sort for a table calculation
          Matt Lutton

          I have not taken the time to build it, but I think I have the solution in my head using LOD expressions.  If so, I'll rate this one as a 2, as it is fairly simple once you know the syntax and concepts.  If LOD isn't key here, feel free to let me know and I will give it some more thought!

          1 of 1 people found this helpful
          • 2. Re: Educational Brain Teaser: One-click sort for a table calculation
            Shawn Wallwork

            Got it but a bit of fiddlin' so I'll go with a 3.

             

            --Shawn

            • 3. Re: Educational Brain Teaser: One-click sort for a table calculation
              Jim Wahl

              Good brain teaser -- started off as a 1, but increased to a 2/3 as I tried variations. If you understand how Tableau works, it makes sense, but it can be confusing if you don't.

               

              Related brain teaser: Can you disable the sort icon? For example in the below calendar, I want to be able to select a column of Thursdays, but I don't want the user to accidentally press the sort icon. In the right-hand image below, you there is no sort icon.

               

              2015-06-16_10-31-50.png

               

               

               

               

              SPOILER ALERT ---Posting my notes while people are still looking at the thread -- would appreciate any corrections.

               

              One-click Sort Is Available With Original, TC-based sort

              But it doesn't persist across changes to the region filter.

              2015-06-16_05-19-55.png

               

              An advantage of this the TC-based sort is that it works if multiple regions are selected and Regions is on the row shelf. In this case, the sub-cats are ranked / sorted.

               

              Various Approaches

                          

              NumNameColumnsRowsColorsFilterBehavior of Sub-Category Sort IconBehavior of Axis Sort IconBehavior After Changing FilterNotes
              1Orig Problem%Total Sales:
              SUM(Sales)  / TOTAL(SUM(Sales))
              Sub-CategoryRegionRegion TC:
              Lookup(ATTR(Region), 0)
              Sorts by sub-category nameSorts by  %Total SalesMaintains sub-category -- does not re-sort by sales. No default sort order and sort order does not persist across changes to the filter. 

              WHY: Table calc sort option not available in Sub-Category formatting, because table calcs are computed after the sort order for the dimension is applied --- necessary because sort order can affect Table calcs.
              2Orig SolutionSame as 1-1*[%Total Sales], Sub-CategorySame as 1Same as 1DisabledSorts by  %Total SalesOn first view, shows default. Subsequently shows previous order. Same as 1, with default sort order determined by the left-most discrete pill AND manual sort on Sub-Category disabled.

              WHY: By default, Tableau sorts by the left-most discrete pill, ascending. Since this is a table calc, it is computed after any dimension sort order changes and, therefore, even post-display, manual sorts on Sub-Category are superseded by the table calc. Clicking the axis sort sets a manual sort order in Tableau, which is why it's possible to sort different regions independently (new / refreshed data will break the sort).
              3LOD Fixed%Total Sales Fixed:
              SUM(Sales) / SUM({fixed [Sub-Category] : SUM(Sales)})
              Sub-CategorySame as 1RegionSorts by sub-category nameSorts by  %Total Sales

              %Total Sales Fixed is a regular measure, which means it can be used to set the default sort order for the Sub-Category dimension.

               

              NOTES: Must use regular Region filter and not Region TC filter. Using a TC filter results in same behavior as (2), since this filter is not applied at the time of the sort and, therefore, all of the values are 1.0.

              4LOD Exclude%Total Sales Exclude:
              SUM([Sales]) / SUM({exclude [Region] : SUM(Sales)})
              -1*[%Total Sales Exclude], Sub-CategorySame as 1Region TC:
              Lookup(ATTR(Region), 0)
              DisabledDisabledSimilar to (2), but the sort icon on the axis only sort ascending. Not sure why this is different than (2).
              5Blend% Total Sales Blend:
              SUM([Sales]) / SUM([Sample - Superstore (copy)].[Sales])
              Sub-CategorySame as 1RegionSorts by sub-category nameSorts by  %Total SalesMaintains sub-category -- does not re-sort by sales. Same as 1.
              • 4. Re: Educational Brain Teaser: One-click sort for a table calculation
                Simon Runc

                hi Jonanthan,

                 

                Nice challenge, and another good one for 'understanding how Tableau thinks'...after a bit of messing on I'd give this a 2.5

                • 5. Re: Educational Brain Teaser: One-click sort for a table calculation
                  Simon Runc

                  hi Jim,

                   

                  I'd give that one a 2, although had I not just done the example from Jonathan, I don't think I'd have twigged on how to get the coloring in without re-invoking the 'click-sort' (having managed to remove the 'click-sort' from the text vales)...so would have been a 3

                   

                  Again, a really good example on 'thinking like Tableau'!! Many Thanks

                  • 6. Re: Educational Brain Teaser: One-click sort for a table calculation
                    Joshua Milligan

                    Like Matt, my first thought was LOD.  I'll give it a 2 with that approach. Some of the comments here lead me to suspect other possibilities I'm curious to see what other approaches were considered..

                    • 8. Re: Educational Brain Teaser: One-click sort for a table calculation
                      Joshua Milligan

                      Oh, that's nice!  When I first saw it I thought, "that's still just setting it to manual".  And indeed it is, but Rank works nicely with the manual sort.  (Technically, it takes two clicks -- but still a great approach!)

                       

                      Regards,

                      Joshua

                      • 9. Re: Educational Brain Teaser: One-click sort for a table calculation
                        mark.welsh

                        Just want to comment that this is by far the easiest method I've found so far!  Thanks to everyone who contributed - this is great stuff.