Home > Tableau > Tableau Dynamic Sorting

Tableau Dynamic Sorting

Recently I created a tabular report where it was a requirement for the user to be able to sort the list by all of the different measure columns. I generally publish my reports on to Tableau Server where sorting is easy by using the Tableau toolbar but I wanted to see how to do this without using the toolbar.

I began to think about ways to do this using parameters and calculated fields as a Dimension has the option of Sort By Field. Luckily I didn’t have to think to hard about how to do this as I stumbled across a blog post which describes how to set up dynamic sorting with Tableau. I slightly modified the more complicated of the 2 techniques described in that article, which I describe here.

For this example I’m going to use the Superstore Sales sample data Tableau provide and create a table showing the Customer State on the rows and the measure values Profit, Profit Ratio and Sales in the columns. If unsure how to do this check my other post on displaying data in tableau as a table.

Excerpt of Sales by state unsorted

I am going to make use of parameters and calculated fields to enable the user whether they want to sort this table by Profit, Profit Ratio or Sales.

The first step is to create a parameter called SortBy which contains the names of these fields for the user to select.

Image of Tableau SortBy Parameter creation

Next step is to create another parameter, SortOrder, which the users chooses whether to sort Ascending or Descending. You’ll notice the value returned by the parameter is either 1 or -1. This is because I use this later when setting the sorting calculation.

Image of Tableau SortOrder Parameter creation

The parameters needed are now created, the next step is to create some calculated fields which use the values returned to sort the table. This calculated field is called SortStateField and uses the values returned by both parameters.

Tableau create a calculated field to sort

In the SortOrder parameter a 1 or -1 is returned. The parameter was set up as a String so the first thing to do is convert it to a number using the INT function. This can then be used to multiple the values to either sort ascending or descending. Another thing to note is the Profit and Sales are both aggregated in the calculated field. The reason for this is Profit Ratio is pre-aggregated and Tableau does not allow the combining or aggregate and non-aggregate functions meaning the non-aggregate fields need to be aggregated in the calculation to avoid an error.

The final step is to set the Customer State pill on the rows shelf to sort by this calculated field. Right click on the pill and click Sort. In the Sort options there is a choice to Sort by Field. Sort by the SortStateField calculated field. The aggregation will default to Custom as all aggregations are already predefined in the field.

Tableau Sort By Field

Display the parameters on the worksheet and it’s complete, the user can now choose how to sort the table.

  1. Louise H
    July 8th, 2013 at 12:27 | #1

    This was really helpful! Thanks for publishing it. 🙂

  2. Harshad Hardas
    March 3rd, 2014 at 11:28 | #2

    Perfectly explained.. Done
    Thanks a lot.. HH

  3. Andrew L
    February 6th, 2015 at 00:38 | #3

    Awesome, article and step-by-step directions!

  4. ranly
    June 22nd, 2015 at 03:43 | #4

    I’m using T9.0. the field SortStateOrder not available when sort by field
    Pls advise. tks

    • awatson
      August 12th, 2015 at 20:04 | #5

      Hi, I think you could be looking for the wrong field. The sorting field is called SortStateField. I’ve updated the post to show the sorting.

  5. SM
    August 27th, 2015 at 17:32 | #6

    Thanks for the post; What if we want to sort ‘date’ not a number list?

    SM

    • awatson
      August 27th, 2015 at 20:38 | #7

      Behind the scenes a date is a number so this technique should (without me actually trying…) work. If not another option is to convert the date to a number for sorting. For example you could build the date based on YearMonthDay – for example 27 Aug 2015 would be 20150827 or 1 Aug 2015 would be 20140801. Converting your dates to numbers like this always sorts correctly but may be completely unnecessary as you might be able to sort by date anyway or just wrapping the date in an INT() function might do it. Let me know how you get on.

  6. BI Follower
    October 6th, 2015 at 18:57 | #8

    Excellent Tip.. I just implemented for my Analytics

  7. rggg
    October 12th, 2015 at 21:52 | #9

    I am using three metrics from three different data sources ( I have joined all three by using”Edit relationships”) but while sorting on my dimension I do not see the calculated field (equivalent of SortStateField). I suspect it has something to do with the fact that there are different data sources involved. Can you think of a work around?

    • awatson
      October 16th, 2015 at 21:06 | #10

      It gets tricky once you start using multiple data sources. Can you supply a twbx file as an example and I’ll take a look?

  8. Hanna
    December 10th, 2015 at 13:49 | #11

    @awatson
    hi, I have the same problem, need to sort 2 measure values which come from 2 different data sources. Your solution works well only in the value from primary data source, but not the other. Can you help? Thank you.

    • awatson
      December 11th, 2015 at 17:00 | #12

      This is a bit more tricky but still possible. As per the post still create the sorting field, but don’t use it in the Sort area of the field – this will error as you probably noticed. Instead drag the sort by field into the rows, set it as a discrete value, make sure it’s the FIRST value on your rows shelf, as Tableau sorts by the first value by default.

      The default sort if ascending, which could mean you need to get a bit smart with multiplying by -1 and 1 to get the correct sort order. You can do this by tweaking the SortBy calculated a little.

      I also suggest hiding the field by unchecking Show Header once it’s working.

      Hope that helps.

  9. Michael Puli
    March 21st, 2016 at 14:22 | #13

    Can you please suggest a work around if all the fields are text fields?? and one field is a date field. can we still achieve this functionality?

    • awatson
      March 21st, 2016 at 21:44 | #14

      A date field could be converted to a number for the purposes of sorting – i.e. 1 May 2016 could be converted to 20160501. That format will always sort correctly.

  10. May 14th, 2016 at 05:31 | #15

    Can anybody help me out how to sort all Dimensions(Atleast 1) and 1 or More Measure(s) in a view in Tableau ?

  11. Dave
    October 28th, 2016 at 02:57 | #16

    Hi, shouldn’t descending SortOrder be +1 and ascending be -1?

  12. lavanya
    November 2nd, 2016 at 17:02 | #17

    THANKS ALOT…..VERY EASY WAY EXPLAINED

  13. Abhinav
    February 20th, 2017 at 10:35 | #18

    While applying the sort on profit margin the numbers are not actually sorted accordingly..

  14. Aravind
    April 21st, 2017 at 12:24 | #19

    This is awesome. But I have a question here, how to we do sort order for multiple dimension columns A-Z to Z-A and vice versa?

  1. No trackbacks yet.