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:

  1. 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.

  2. 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.

  3. 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:

  1. Weekly: DATETRUNC("week", [date], "monday")

  2. 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

  1. 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.

In order to create these table calculations, in the right Data pane right click and choose Create Calculated Field.

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:

Now create a new calculated field named DateTotal:

CASE [Moving Averages Type]

WHEN 'Weekly' THEN [Weekly]

WHEN 'Biweekly' THEN [Biweekly]

WHEN 'Ten Days' THEN [TenDays]

WHEN 'None' THEN [Date]

END

Finally, right click on the Moving Averages Type parameter and choose Show Parameter to give user an access to change this parameter.

Finally, you should see the toolbar to change this parameter in the right side of the sheet.

Calculating moving averages

Now it's really easy to visualize the moving averages. Put DateTotal into the Columns and Cases into the Rows and County to the Markers and change it color. Now the moving averages are visualized.

Comparing measures

It is possible to compare measures by creating parameters. The aim in this section is to compare the number of cases of COVID-19 in each month. As a result, we will create a new parameter and call it Month Parameter.

Now create a new calculated field and call it SlicedCases:

iif(DATETRUNC('month', [Date]) = [Month Parameter],[Cases],null)

Now create a new sheet and visualize the cases in each month that could be interactively changed by the user.

The workbook that could be used to reproduce the visualizations in this section is shared here: https://bit.ly/36dMSPN