Table Calculations in Tableau are like SQL Window Functions
Table calculations are functions that are applied on top of aggregations in the result set1. For example, if we have sum of Sales by Sub-Category and we want to return rank of sum of Sales by Sub-Category, that’s where table calculations come in.
In that scenario, Sum([Sales]) by Sub-Category makes up our result set. And Rank() is the table calculation that is applied on top of the existing aggregate calc, Sum().
Table calculations are executed in memory in Tableau, as opposed to aggregate and row-level calcs which are applied in the database. Because table calculations do not occur in the database, they are not translated into SQL. However, the behavior of table calculations mirror a concept in SQL — window functions.
The defining feature of a window function is the OVER clause. Let’s look at an example of the rank table calc in Tableau. Then, we’ll show a SQL window function that returns the same results.
The SQL OVER statement applies the RANK() on top of the SUM(Sales) - pretty much the same thing that Tableau does. The ORDER BY clause tells SQL the order in which we want to apply our Rank() function.
Let’s look at a more complicated example that ranks sum of Sales by Sub-Category within each Category — in other words, a nested rank.
To make this work in Tableau, we need to select compute using either Sub-Category or one of the Pane options.
In order to mirror this functionality in a SQL window function, we need to use Partition BY. As the name suggests, this tells SQL to perform the rank function for each partition — in our case, for each Category.
Notice that in SQL, the Category field is what follows PARTITION BY, whereas in Tableau, we check the box for compute using Sub-Category.
I hope you enjoyed exploring the connections between Tableau table calcs and SQL. If you have any questions or thoughts, please let me know in the comments!
Your result set is the collection of marks in your Tableau worksheet