Do you want to know how to sort a Tableau table? Sometimes the user may want to sort by a measure, sometimes by a dimension.
Sorting a table in Tableau can get complicated. This post will run through simple sorting, that comes “out of the box”, to more complex sorting using parameters and calculated fields.
Sort a Tableau table by Measure
Often users of your Tableau dashboards will want to know how to sort a table by different measures. If your table is a simple measure names / measure values this is very simple. Hold the mouse over the column name in the title and a small sorting icon appears.
However often tables are more complex. Perhaps the table was constructed using the placeholder technique, enabling a mixture of dimensions and measures to be displayed in the same table, along with more advanced conditional formatting. These may mean the simple columnar sorting doesn’t work.
Sort By A Calculated Field in Tableau
Using a mixture of parameters and calculated fields, it is possible to create custom and dynamic sorts within Tableau.
To sort by a calculated field there are 2 options:
- Use the field in the Sort – Field section of the field sort menu
- Place the sorting calculated field as a discrete (blue) pill to the left of the field(s) to sort
For this example, I’m going to use the Superstore Sales sample data that Tableau provide.
I will create a table showing the Customer State on the rows and Profit, Profit Ratio and Sales on columns. For this example I use the Measure Names / Measure Values technique to build the table. If unsure how to do this check my other post on displaying data in tableau as a table.
I understand with a simple table, such as this example, sorting is simple using the Sort icon that appears when hovering over a column header. However the purpose of the post is to show a way to sort using a calculation.
Making use of parameters and calculated fields, the user can choose whether they want to sort this table by Profit, Profit Ratio or Sales. They will be able to sort Ascending or Descending.
Create the parameters and calculated field for sorting
The first step is to create a parameter called SortBy. This contains the names of these fields for the user to select.
Next step is to create another parameter, SortOrder, which allows the user to choose whether to sort Ascending or Descending. You’ll notice the value returned by the parameter is either 1 or -1. This is needed within the sorting calculation.
Now we have created the parameters the next step is to create a calculated field using those parameter values.
This calculated field is the sorting calculation.
The SortOrder parameter returns either a 1 or -1.
However, as the parameter type is a String the values returned are strings.
Therefore, use the INT function to convert the 1 or -1 to a number.
Once it’s a number, use it to sort ascending or descending simply by multiplying.
Multiplying by -1 will convert the largest number to the smallest and vice versa.
Note the aggregation in the formula of both the Profit and Sales. Tableau does not allow combining of aggregate and non-aggregate functions.
Profit Ratio is pre-aggregated, therefore there is no choice but to aggregate all of the fields in the formula.
To implement the sort there are two options
Sort using the Sort – Field in the pill Sort menu
One is to use the calculated field to sort the [State] pill. Right click on the pill on the Rows shelf and click Sort. In the sort options there is a choice to Sort by Field. Sort by the SortStateField calculated field. As the aggregations are already predefined in the calculation “Aggregation” will default to Custom.
Sort by putting the calculated field on Rows
The second option is to place the SortStateField on the Rows shelf.
Set it to be Discrete (blue) and drag it to the far left, so is it the first pill on rows. It has to be Discrete; Continuous (green) pills are always on the right, it’s not possible to place green pills to the left of blue.
To make the sorting calculated field not visible simply uncheck “Show Header” on the pill.
Which ever way you implement the sort, the final step is to display the parameters on the worksheet.
Now the sorting by calculated field is complete and the user can choose how to sort the table.
Sorting a Tableau table by a dimension
The question about sorting alphabetically has come up a number of times. Sorting A-Z is actually really simple, it is the default sort behaviour.
Add another value into the parameter, call it “Alphabetic”. The actual text doesn’t matter, the important thing is only one additional value is added to the parameter.
A minor edit of the sorting calculated field will now enable to alphabetic sorting. Add an Else component to the IF statement; ELSE 1 will do the job. The SortStateField will become:
This will now sort alphabetically, A-Z, whenever Profit, Profit Ratio and Sales are NOT selected.
Sort alphabetically using a formula
Another alternative, with limitations, is to use the ASCII formula. The ASCII formula converts the first letter to a number. That is the limitation, it sorts only by the first letter.
However, there is a workaround.
To sort the Customer State field alphabetically (by the first letter only), use the formula:
To sort this descending, multiply by the SortOrder.
MIN(ASCII(UPPER([Customer State]))) * INT([SortOrder])
To sort alphabetically by more letters, increase the complexity of the calculation.
For example, this formula sorts by the first 3 characters:
ASCII(UPPER([Customer State])) + ASCII(UPPER(MID([Customer State],2,1))) * 0.1 + ASCII(UPPER(MID([Customer State],3,1))) * 0.01
Adding additional letters is simple – alter the number in the MID function to match the position in the string and another 0 to the right of the decimal point.
Note: this is only really useful to sort alphabetically descending. Sorting alphabetically ascending is better using the default alphabetical sort order.
The final formula to sort alphabetically both ascending and descending (with aforementioned caveats) is:
The // in the formula are comments, that’s how to add comments to a calculated field in Tableau.
In the Tableau workbook, sort Descending by State and check New Jersey, New Mexico and New York. Notice they don’t sort correctly as the formula only sorts Z-A for the first 3 letters.