[TABLEAU] Sorting Concepts Related To Quick Table Calculations

Sorting Concepts Related to Quick Table Calculations

Tableau provides extensive sorting capabilities. Tableau Quick Table Calculations sometimes provide inconclusive results. This article describes those situations and solutions for them. Quick Table Calculations and Tableau Computed Sorts When you right-click a measure in the view, a context menu opens that offers: Add Table Calculation Quick Table Calculation The top item, Add Table Calculation, opens the Sort dialog box, in which you make selections to sort this measure's data by another field in the data source. This is also referred to as a "computed sort." The Sort dialog box never displays or includes Quick Table Calculations. This is because Tableau Quick Table Calculations occur as a post-database processing step, and Tableau does not go back to the underlying data to perform a second pass through the data simply to sort the Tableau Quick Table Calculations results. Below is an example of a table calculation. You can achieve this same result by using the one-click sort buttons on the toolbar. Situations in which a one-click sort works well One-click sort works well for running totals, percent of totals, and differences from discrete elements (first, last, "named" element, etc.). In the image below, you can see that sorting by "percent of total" works well. This is because the calculated percentage is relative to the actual values in the data. The same result applies to the following types of Tableau Quick Table Calculations: Running Totals Difference From First/Last Difference from a "Named" Value Percent Difference from First/Last Percent Difference from a "Named" Value Situations in which a one-click sort provides inconclusive results Certain types of Tableau Quick Table Calculations create inconclusive results when you use the one-click sort. These include: moving average difference from "previous" percent difference from "previous" All of these types of calculations use relative positions for running the calculation, and if you re-sort the dimensions, the relative positions change, creating inconclusive results. Here is a view showing a moving average across months of the year. The calculation uses the previous period, current period, and next period to calculate the moving average of sales. Notice that April is highlighted with a value of 1699.10. If you then perform a one-click sort, Tableau uses the original "average sales" amount to perform the sort. The months are re-arranged. And more importantly, the moving average of sales changes for April. Using Tableau sets to correctly sort calculations A solution to the situation shown in the previous section is to create a Tableau set and force a proper sort for a Tableau Quick Table Calculation. Because the set is separate from the original data, Tableau can act independently upon it. Step 1 Create a calculated field for month: MONTH([Order Date]). Step 2 Create a Tableau set from this new calculation and call it month sorter. Step 3 Place this new set on the Level of Detail shelf. Step 4 When creating your Difference From Table Calculation, in the list entitled Calculate the difference along, select your new set instead of Table (Across), etc. Step 5 Now when you use the one-click sort, it will sort the months, but continue to use your new "sorting set" to perform the calculation. Because there are two month fields in play, Tableau understands how to achieve this. In the image below, you can see that the months are out of order, but the information is correctly sorted. We have also provided a finished packaged workbook (upper right corner of this article) which demonstrates this example nicely.