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:
MIN(ASCII(UPPER([Customer State])))
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.
This was really helpful! Thanks for publishing it. 🙂
Perfectly explained.. Done
Thanks a lot.. HH
Awesome, article and step-by-step directions!
I’m using T9.0. the field SortStateOrder not available when sort by field
Pls advise. tks
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.
Thanks for the post; What if we want to sort ‘date’ not a number list?
SM
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.
Excellent Tip.. I just implemented for my Analytics
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?
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?
@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.
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.
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?
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.
Can anybody help me out how to sort all Dimensions(Atleast 1) and 1 or More Measure(s) in a view in Tableau ?
Hi, shouldn’t descending SortOrder be +1 and ascending be -1?
THANKS ALOT…..VERY EASY WAY EXPLAINED
While applying the sort on profit margin the numbers are not actually sorted accordingly..
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?
Hello everyone
my requirement i want to sort by category,region,segment with asending or desending order .can you please help me here.
it is working sorting with country,state,city used calculation is CASE [dimension]
WHEN “catagory” THEN [Category]
WHEN “segmnet” THEN [Customer Segment]
WHEN “region” THEN [Region]
END
but not wrking with asending or descending order..plg help me with calculation.
No dev…for 1 asending and -1 for desending..then only it working fine
Hi, it’s not clear, did you manage to resolve this or still would like some assistance?
Hi,
Is the multiple dimensions sorting with Ascending or Descending order working?
Not 100% sure what you mean. Are you asking about sorting alphabetically?
@awatson
I’ve set this up for my data but I’m having trouble getting it to work with my Set and I believe the problem is something to do with me using Year as a filter.
In the Top tab under Edit Set, if I sort by Field and select SortStateField then my data in the Set sorts by ALL years rather than the year selected in the Filter. I have a feeling I may be able to rectify this using the By Formula button in the Edit Set – do you have any idea if that would be possible?
This may be related to Tableau’s order of operations. Try adding the Year filter to context, see if that resolves the issue.
Hi there, this is really helpful. However i have some difficulties here. I have 2 calculated measures that using str() to convert value to string and have it in parameter so user can either view it by number or percentage as i want to display the value with % when percentage in parameter is selected. But once i have done this the sort icon by default on colomn header in no longer there. Is there anyway around this?
In that case I think you have little choice but to sort using a parameter instead of the sort icons. Or you show the 2 measures (% and actual) at the same time, keeping them as numbers so the icon appears.