Custom Sorting Options Make Tableau Dashboards More User-Friendly
Tableau has made great improvements to its sorting features over the years, such as nested sorting in version 2018.2. This has helped avoid user confusion when sorting, but sometimes the default options and user interface just don’t cut it. For instance, when displaying multiple measures on the columns shelf, Tableau places the axes header at the bottom of the pane. The sort icon appears at the top when hovering, but this isn’t always ideal for new users, and if you create custom headers, you’ll need a different option. In this post, I will show you how to leverage parameters to create custom sort options for your dashboard.
Click here to view the workbook on Tableau Public.
Create the Sheet You Plan to Custom Sort
Using the standard sample superstore data set, I will illustrate the scenario above and show you how to create a parameter to enable custom sorting. Place State on the rows shelf and place Sales, Profit, and Profit Ratio on the columns shelf. Choose bar as the mark type for all of the measures. You should have a sheet that looks like this.
Create the Parameter for Custom Sorting
Next let’s create the parameter. Select string as the data type, call it Sort By, and enter in the measure names as the values.
Create the Calculated Field for Custom Sorting
Next we’ll create a calculated field that references the parameter selection. Call it Sort By Field and use the syntax below.
Using a case statement and evaluating the Sort By parameter, we can dynamically select which field is referenced. The Sum and Profit fields both need to be aggregated, while Profit Ratio does not since it is already aggregated.
Set the Sheet Sorting
This last step brings it all together by setting the State field in our sheet to use the Sort By field. Right-click on the State pill in the columns shelf and select Sort.
Next, select Field in the Sort By dropdown, select Descending for the Sort Order, and choose the Sort By Field we just created as the Field Name.
Your sheet should now look as follows if Sales is selected as your Sort By measure. The user now has the ability to sort by each measure via the parameter.
Bonus: Sorting by Measures and Dimensions
We have successfully created a parameter to sort by the measures in our sheet, but what if the user wants to also sort by State? This can be achieved by adding State to our parameter and calculated field, but will require converting field types since CASE statements require all evaluated fields to be the same (string, float, int, etc).
The existing fields in our Sort By Field are all float. When we insert State into the CASE statement, we receive an error because State is a string.
To correct this error, wrap State in an ASCII() function to convert it to a number.
Now we get a different error because the measures are aggregated, but the State field is not. To correct this, wrap the expression in a MIN() function. Any other aggregate function would work here as well.
After aggregating State, the CASE statement is valid again, and the user can now sort by every field in the sheet.
Bonus Bonus: Sort Ascending or Descending
The only custom sort functionality we have not created yet is the ability to select the sort order as ascending or descending. To do so, we need to create another parameter and edit our Sort By calculated field.
First create a new parameter called Sort Order with the following settings.
Next edit the Sort By Field to include the Sort Order parameter. This will require a nested CASE statement. The trick here is to multiply the result returned by the ‘Descending’ value by -1 to reverse the order.
The last step in adding the Sort Order parameter is to change the Sort Order of the State field on the Rows shelf. We set it to Descending earlier, but now that we have revert back to Ascending to comply with the logic in the Sort By Field.
Recap of how to create custom sorting with parameters in Tableau
We centralized the sorting functionality into one parameter to provide users with a more intuitive way to sort the sheet than hovering over the header to show the sort icon.. The Sort By and Sort Order parameters can conveniently be placed anywhere on the dashboard to provide a streamlined user experience. See the finished product below and click to view on Tableau Public.