KNOWLEDGE BASE

Sorting Dimensions by Calculated Fields That Use Table Calculations


Published: 03 Jun 2014
Last Modified Date: 20 Jul 2023

Question

How to sort a dimension by a calculated field that uses a table calculation.

Environment

Tableau Desktop

Answer

Fields using table calculations are not currently available in default sorting options. In general, a view can be sorted by a table calculation by placing a copy of the table calculation as a discrete field in the first position on the Rows shelf and hiding it.

Table calculations allow for a lot of customization and thus the solution needed will differ based on the original view. The following directions use the Superstore sample data set to go through 3 examples with increasing complexity. These directions are demonstrated in the "Sorting by Table Calc_v2022.1.twbx" workbook, which is downloadable from the right-hand pane of this article.

Example 1: Sort subcategories by percent of total sales

These directions start from the "Example 1: sort by % of total sales" view. Directions to create the "Example 1: sort by % of total sales" view are included in the attached workbook
  1. Select Analysis > Create Calculated Field
  2. In the Calculated Field dialog box that opens, do the following, and then click OK:
    1. Name the calculated field. In this example, the calculated field is named "% of total sort order"
    2. Drag the green SUM(Sales) field with the triangle icon from Columns shelf into the calculation editor. Tableau will write out the formula for the quick table calculation.
    3. Optional: multiple the generated formula by -1 for descending order in the final view
  3. Right-click [% of total sort order] in the left-hand data pane and select Convert to Discrete
  4. Drag [% of total sort order] to the first position on the Rows shelf
  5. Right-click [% of total sort order] on the Rows shelf and uncheck Show Header

Example 2: Sort subcategories by percent difference sales for most recent year

These directions start from the "Example 2: sort by 2015 % Difference" view. Directions to create the "Example 2: sort by 2015 % Difference" view are included in the attached workbook
  1. Select Analysis > Create Calculated Field
  2. In the Calculated Field dialog box that opens, do the following, and then click OK:
    1. Name the calculated field. In this example, the calculated field is named "Most recent Year % Diff Sort Order"
    2. Write out the following IF THEN statement:
      WINDOW_SUM(
          IF LAST() = 0
          THEN 
      
          END
      )

      The IF THEN statement filters to the last column in the view. The WINDOW_SUM() function returns the same filtered value for every year within a sub-category. 
    3. Drag the green SUM(Sales) field with the triangle icon from Label into the calculation editor between "THEN" and "END". Tableau will write out the formula for the quick table calculation
    4. Optional: multiple the entire formula by -1 for descending order in the final view
  3. Right-click [Most recent Year % Diff Sort Order] in the left-hand data pane and select Convert to Discrete
  4. Drag [Most recent Year % Diff Sort Order] to the first position on the Rows shelf
  5. Right-click [Most recent Year % Diff Sort Order] on the Rows shelf and uncheck Show Header


Example 3: Sort Subcategories by percent of total sales for user selected year

These directions start from the "Example 3: sort by selected % of Total" view. Directions to create the "Example 3: sort by selected % of Total" view are included in the attached workbook
  1. Select Analysis > Create Calculated Field
  2. In the Calculated Field dialog box that opens, do the following, and then click OK:
    1. Name the calculated field. In this example, the calculated field is named "Year of Order Date"
    2. In the formula field, create a calculation similar to the following:
      DATEPART('year', [Order Date])
  3. Right-click [Year of Order Date] in the left-hand data pane and select Create > Parameter...
  4. In the Create Parameter dialog, do the following and click OK:
    1. Name the parameter. In this example, the parameter is named "Select a Year to Sort View"
    2. In the Display format dropdown select Number (Custom), 0 decimal places, and uncheck Include thousands separator 
    3. Select When workbook opens, and in the dropdown select Year of Order Date
  5. Create a calculated field named "% of total" and drag the green SUM(Sales) field with the triangle icon from Label into the calculation editor. Tableau will write out the formula for the quick table calculation
  6. Create a calculated field named "Selected Year % of Total Sort Order" with a formula like:
    -1 *
    WINDOW_SUM( 
        IF MIN(YEAR([Order Date])) = [Select a Year to Sort View]
        THEN [% of total]
        END
    )

    Multiplying by -1 results in descending sort order in the final view
  7. Right-click [Selected Year % of Total Sort Order] in the left-hand data pane and select Convert to Discrete
  8. Drag [Selected Year % of Total Sort Order] to the first position on the Rows shelf
  9. Right-click [Selected Year % of Total Sort Order]  on the Rows shelf and select Edit Table Calculation...
  10. In the Table Calculation dialog, do the following and close the dialog:
    1. In the Nested Calculations dropdown, select % of total
    2. Under Compute Use select Table (down)
      Note: The [% of total] field referenced inside of [Selected Year % of Total Sort Order] can be computed differently than the quick table calculation in the view, therefore we must ensure that the percent of total is being computed the same.
  11. Right-click [Selected Year % of Total Sort Order] on the Rows shelf and uncheck Show Header

 

Additional Information

Ultimately the goal is to create a calculated field that returns a value to sort the view:
  • Example 1 shows how to sort a simple bar chart where there is only 1 dimension on the Rows shelf
  • Example 2 sorts every row in a highlight table by the value in the last column. The sort order calculation must return just the 2015 percent difference sales value, but it must return the 2015 value for every year on the same row. Otherwise, each year will be sorted separately.
  • Example 3 also sorts a highlight chart by the value in a specific column, but this time the table calculation in the view is computed differently than the table calculation used to create the sort order. This requires setting advanced compute using settings to get the correct results.

The tooltip from a header made from a dimension will have sort icons even when the view contains table calculations; However, this option creates a manual sort based on the current values. The sort created from the header tooltip will not update when the data updates.

For more information on how computing table calculations differently creates different results, see Transform Values with Table Calculations and Nested Table Calculations
Did this article resolve the issue?