Table Calculation in Tableau Desktop
advanced table calculations
There are lots of builtin functions in Tableau Desktop that could be used for customization of table calculations. For example, we want to calculate moving averages of cases and deaths of COVID-19 across different counties of South Carolina state. This moving average could be calculated on a weekly, biweekly, or ten days basis. These time windows could be calculated by using advanced table calculations as:
Weekly: we need to truncate the timeline or date field into weekly chunks to be able to calculate the moving averages on a weekly basis.
Biweekly: we need to truncate the timeline or date field into biweekly chunks to be able to calculate the moving averages on a biweekly basis.
Ten days window: we need to truncate the timeline or date field into ten days chunks to be able to calculate the moving averages on a ten days basis.
Each of these calculations for weekly, biweekly, or ten days window could be done by using Create Calculated Field. The formula for each time window is provided here:
Weekly: DATETRUNC("week", [date], "monday")
Biweekly:
IF DATEDIFF('week', DATETRUNC('week', [date]), #2020-01-01#, 'monday') % 2 = 0 THEN
DATEADD('week', 2, DATETRUNC('week', [date])) -1
ELSE
DATEADD('week', 1, DATETRUNC('week', [date])) -1
END
Ten days window:
IF INT([date]) % 10 = 0 THEN
[date]
ELSE
[date] - INT([date]) % 10
END
In the above formulas for weekly, we truncate the date field into weekly chunks by using built-in function of Tableau as DATETRUNC where the first argument tells the chunk, which is week here, the second argument tells the name of the field to be truncated, and the third argument shows the first day of the week.
Furthermore, one of the most common functions to calculate an average in a given window is WINDOW_AVG. It is possible to calculate ten days moving average by using this formula: WINDOW_AVG(SUM([Cases]),-10,0)
A complete list of table functions available in Tableau Desktop is available here: https://help.tableau.com/current/pro/desktop/en-us/functions_all_alphabetical.htm
Creating parameters
Creating parameters are used to customize the user experience. In this example and in the previous section, we saw that we could calculate the moving averages based on three different time frames. As a result, let's create a parameter to easily switch between these three different time frames. In order to create a new parameter, right click on the Data pane and choose Create Parameter.
Now change the name of this parameter to Moving Averages Type and change the Allowable values to list and put Weekly, Biweekly, Ten Days, and None:
The workbook that could be used to reproduce the visualizations in this section is shared here: https://bit.ly/36dMSPN