Custom Sort Control in Tableau

Mar 14, 2019

Klaus Schulte

This week I had a lot of fun recreating Lorna Eden’s #WorkoutWednesday2019 challenge. One key element was to implement a custom sort control (click to play with the interactive version on Tableau Public):

WoW 11:2019

We should use a parameter to allow a sort by (city) A-Z, (city) Z-A, (profit) asc, and (profit) desc.

I really loved to see how everyone found her or his way to implement this.

Create a Sort Parameter

First thing you need isto create is a sort parameter. I created this one:

Edit Parameter [Sort by] 2019-03-15 15-38-49.jpg

I chose an integer parameter mainly because it is easier to reference in the calculated field you have to create in the next step.

Create a calculated field

Basically, there are two ways to do it:

  1. Create a sort-field without table calculations, that can be selected in the dimension field under ‘sort by -> field’ (you cannot use table calculation as a ‘sort by’-field).
    You can do this by using the ASCII-formula. ASCII Returns the ASCII code for the first character of a string, for example 65 for A and 66 for B.
    Tableau - Profit- Where? and What? WoW 2019:11 [from PUBLIC.TABLEAU.COM (DEFAULT)] 2019-03-14 22-12-30
    For the A-Z and Z-A order you can then sort on avg(ASCII_code) for ascending and
    -avg(ASCII_code) for descending. For sorting by profit you can sort on sum(profit) for descending and -sum(profit) for ascending.
  2. Create a calculation that includes table calculations, make it discrete and use it as a hidden dimension in the view (that’s how I did it).
    Here’s my calculated field:
    Dialog 2019-03-14 22-16-02.jpg

    I love how you can use one identical formula for both sorting on numbers and strings. And I love how the rank formula allows you to define the sort order explicitly, instead of using a negative sign like shown in the first approach.

Implement the sort field in the view

Last step is to implemented it in the view:

Tableau - Profit- Where? and What? WoW 2019:11 [from PUBLIC.TABLEAU.COM (DEFAULT)] 2019-03-15 15-51-45

Make sure to calculate the ranks on the right level of detail. In my case, I used a combined field of city and abbreviation, that’s why I had to compute my ranks using these dimensions.

Tableau - Profit- Where? and What? WoW 2019:11 [from PUBLIC.TABLEAU.COM (DEFAULT)] 2019-03-14 22-17-00.jpg

For the final view I hid the sort view header and was ready to go.

Nice! 😊

There are many use cases for sort controls like this, allowing users to play with the sorting!

Hope you find own use cases for this and enjoyed reading.

3 COMMENTS


Avatar
May 18, 2020, 10:26:17 AM
Arslaan Munir Khan says:

Do you have a video to explain the same. I am finding it really hard to follow the steps.


Avatar
Mar 21, 2019, 6:12:20 PM
Holger Ihle says:

Thank you very much for sharing this approach. This is very helpful. I’m wondering if this is applicable to a worksheet with more than one column (measure) and multiple rows (categories). Say if you add SUM(sales) to columns and Region to the rows. What would be the neccessary adaptation to add sorting options for sorting by SUM (sales) as well.


    Avatar
    Mar 21, 2019, 8:44:54 PM
    Klaus Schulte says:

    Hi Holger, you can use the same approach. Think I found you on twitter and I will send you a screenshot.