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

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

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:

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

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

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

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

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.

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.

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

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

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

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..

• ###### 7. Re: Educational Brain Teaser: One-click sort for a table calculation
1 of 1 people found this helpful
• ###### 8. Re: Educational Brain Teaser: One-click sort for a table calculation

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

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.