Sort Data in a Visualisation
There are many ways to sort data in Tableau. When viewing a visualisation, data can be sorted using single click options from an axis, header or field label. In the authoring environment, additional sorting options include sorting manually in headers and legends, using the toolbar sort icons or sorting from the sort menu.
Tip: Download the workbook used in these examples to explore on your own (requires Tableau Desktop).
Quickly sort from an axis, header or field label
There are multiple ways to sort a visualisation with single click sort buttons.
In all cases, one click sorts ascending, two clicks sorts descending and three clicks clear the sort.
The sort will update correctly if the underlying data changes.
Sort from an axis
- Hover over a numerical axis to bring up the sort icon.
- Click the icon to sort.
In this example, the sort is applied to Colour (sorting the rows) based on the values for Metric A. If there are hierarchical dimensions like above, the sort is applied to the innermost dimension. Here, that means that Colour will sort inside Hue. Dark magenta cannot sort to the top of the viz because it must stay inside the pane for the Purple hue.
Sort from a header
- Hover over a header to bring up the sort icon.
- Click the icon to sort.
In this example, the sort is applied to Material (sorting the order of the columns – Paint, Fabric and Paper) based on the values for Green, since that header was used for the sort.
Sort from a field label
- Hover over a field label to bring up the sort icon.
The sort icon for a field label is slightly different from a header or axis. The default option is alphabetical sorting, but there is also a menu where you can choose to sort by a field in the view.
- Click the A-Z icon to sort alphabetically, or open the menu to see a list of fields it is possible to sort by then chose a field. The icon switches to the bar icon and you can click to sort.
In this example, the sort is applied to the outermost dimension (Hue) based on total Metric B. (Metric B is aggregated for all the colours within each hue and Hue is sorted. Therefore, Purple is first, then Green, then Blue.)
Missing sort icons
If the sort icons do not appear, this functionality may have been turned off or it might not be possible to sort the view. For example, scatterplots cannot be sorted from a numerical axis because the position of the marks are fully determined by the data. No sort icon will appear on the axes in scatterplots.
Sort options while authoring
In an authoring environment, there are additional sorting options to those available on published content.
Sort from the toolbar
- Select the dimension you wish to sort.
If you do not select a field before sorting, the default behaviour is to sort the deepest dimension.
- Choose the appropriate sort button (ascending or descending) in the toolbar.
For example, in the gif above, the sort is applied to Hue unless the Material field is selected prior to sorting. The toolbar sort also applies to the leftmost measure, in this case Metric B. To sort by Metric A, it would be necessary to reverse their order on the Columns shelf or use another method of sorting. (To see the effect of sorting by Material, Hue is removed from the view. This makes it easier to see how the sort was computed.)
Sort by drag and drop
To manually sort, select a header in a viz or on a legend and drag it to the correct location – a heavy black line indicates where it will go.
Note: Sorting on a legend also changes the order of the marks, not simply how the legend is displayed. Whatever is bottommost in the legend becomes the bottommost mark in the viz. This can either mean closest to the axis or header, or actually underneath in the case of scatterplots and other viz types that may have overlapping marks.
Sort specific fields in the viz
Customise sorting by using the Sort menu for specific fields. (The Sort menu is not available for continuous measures.)
- Right-click (Windows) or control-click (Mac) the field you want to sort, and select Sort.
- Select a Sort By option, and configure its behaviour:
Data Source Order sorts based on how the data is sorted in the data source. Generally for relational data sources, this tends to be natural sort order. Natural sort order is an alphabetic sort where multi-digit numbers are treated as a single character. For example, natural sorting puts 2 before 19 because 2 is less than 19, whereas alphabetic sorting puts 19 before 2 because 1 is less than 2.
If you are using a cube, data source order is the defined hierarchical order of the members within a dimension.
Alphabetic sorts the data alphabetically. This sort is case sensitive, sorts [A-Z] before [a-z], and treats digits individually (that is, 19 comes before 2).
To create a non-case sensitive sort, create a calculated field using the UPPER() or LOWER() functions to remove variation in capitalisation. For more information, see String Functions(Link opens in a new window).
Field lets you select the field whose value will be used to determine the sort order. The field does not need to be used in the visualisation. You can also select an aggregation for the sorting field. The aggregation options available depend on the field type.
Manual lets you select a value and move it to the desired position, either by dragging it in the list or using the arrows to the right.
Nested lets you select the field whose value will be used to determine the sort order. The field does not need to be used in the visualisation. You can also select an aggregation for the sorting field. The aggregation options available depend on the field type.
When the values to be sorted occur in multiple panes, there are two ways to sort: nested and non-nested (or comparative). In the image below, all three values for Hue occur for all three Materials. Nested sorting considers values independently per pane rather than in aggregate across panes.
A nested sort considers each pane independently and sorts the rows per pane.
Purple is sorted above Green for Fabric and Paper, but below Green for Paint.
A non-nested sort considers the value across panes and will have the same order of values per pane.
Purple is above Green for all materials because in aggregate, Purple is higher than Green.
|Nested sorts look correct within the context of the pane, but don’t convey the aggregated information about how the values compare overall.||Non-nested sorts may look incorrect in a single pane, but consistently convey how the aggregated values compare overall.|
|Sorting from an axis gives a nested sort by default.||Sorting from a field label gives a non-nested sort by default.|
Note: When creating a nested sort, the sort is inherited when you drill down through the dimensions. For example, a nested sort on Hue will apply to Colour.
If a dimension has been placed on the same shelf as a measure, that measure will have an axis in the view for each value of the dimension. Using the axis sort icon will generate a nested sort specifically for that value.
In this scenario, an additional field is added to the sort menu dialog for the sorted dimension. To stop breaking down the sort by this value, untick the box for Filter By.
Understanding “incorrect” sorts
It is easy to think a view is sorted incorrectly when in fact the sort logic is simply not clear. A view with a nested sort when a non-nested sort is expected (or vice versa) can be perceived as incorrect. Sorting when there are multiple panes can also give results that seem unsorted.
For example, this view may look like it has not been sorted when in fact it is sorted on Hue by Metric A.
Simplifying the view makes the sort more obvious.
Hovering over the sort icons to see the description of the sort or temporarily simplifying the view can help clarify what is going on.
Sorting can be done from a variety of places in the authoring environment. Although there are icons in the view and on the fields that indicate where a sort is applied, it can sometimes be useful to clear all sorts at once.
To remove the sort on a specific field, right click to open the menu and select Clear Sort.
To remove all sorts in the view, open the menu next to the clear sheet icon in the toolbar and select Clear Sorts. Alternatively, go to Worksheet > Clear > Sorts.
Remove the ability to sort
As an author, you can disable the sort icons in published content. This can be useful to preserve the visualisation’s sort as you built it. Navigate to the Worksheet menu and untick the option to Show Sort Controls. This will prevent the sort icons from appearing when a consumer of the view hovers over an axis, header or field label.
Hiding field labels, headers or the axis will also remove the sort icons.