Sometimes you’ll need to sort your data but you’ve already filtered on a variable, for example wanting to sort/ show the top N products sold within a category/ categories. To do this a parameter, as well as a filter, is needed – but Tableau’s Order of Operations makes this more complicated.
Tableau’s Order of Operations
Tableau performs it’s operations in a certain order, and this determines which data is filtered in the view.
Tableau’s Order of Operations
In the figure above, Tableau works top to bottom, processing commands down the funnel. Extract and datasource filters are followed first, and these are usually applied to your data before any exploratory analysis. These are then followed by dimension and filters, then table calculations which are most often manipulated at the sheet/ analysis level rather than at the datasource level.
Sorting within a filter
1) Put two dimensions on the row (e.g. product category and product name), and a measure on the column (e.g. sum of sales)
2) Add the first dimension to the filter, and select the top 3 or 10 items by sum of sales. This will allow a more compact, useful view in the sheet
3) Create a new calculated field to create a rank calculation, dragging the SUM(Sales) Green pill into the calculation as that can be easier.
4) Drag the rank calculation pill to the rows and, as Tableau automatically adds it to the sheet as a measure, right click and change to discrete. Also move up so it’s between the two dimensions. Remember to right click to change table calculation from table down to pane down
5) Create a parameter, which needs to be an integer. Allow all values to be chosen for now and set a reasonable range, e.g. 1 to 100
6) Right click on the parameter at the bottom of the data pane and select ‘show parameter control’
7) We want to compare the parameter to the rank formula calculated field we created earlier, so create a new calculated field with the formula below. Most of the time you create a parameter (coloured purple in the formula/ calculation window), you also need to create a calculated field that will interact with it
8) Add the new calculated field to the filters shelf where it will act like a table calculation
9) Ensure both the filter table calculation and the rank table calculation (in the row shelf) are acting across the pane, not the table
10) However, you’ll notice that sorting using the parameter is still not quite working. This is due to the order of operations mentioned above meaning that the fixed table calculation that is interacting with the parameter is above the dimension filter. Therefore, we need to make the dimension a context filter by right clicking on the dimension within the filter shelf and selecting ‘Add to Context’. This will make the pill grey
Now it should show the top N products within the product categories (as specified by our parameter)
11) To tidy the view, you can then remove the rank table calculation from the row shelf
Joe Macari talks about Tableau’s Order of Operations in more detail, relating them to Table Calculations and LODs.