In this blog, I will cover quick table calculations and an example using Yahoo’s Finance dataset on the daily price of Bitcoin (BTC/USD) from September 17, 2014 – October 15, 2022.

 

Fundamentals

Table calculations allow us to flexibly obtain values based on what we have in our view. As it clearly states in its name, table calculations are calculations on the table. More specifically, these are applied on aggregated measures that are viewed on a table. The following figure is a list of quick table calculations available in Tableau:

 

Use Case Example

In the finance and investing world, the term simple moving average (SMA) is often tossed around. A SMA is calculated by adding the closing price of the asset for a number of time periods, then dividing this total by the number of time periods which produces the average price of the asset over the time period. For example, if the price of an asset closed at $10, $9, $14 for the past 3 days, then its 3 day moving average for this period is $11.

Analysts and traders use these indicators to help them develop buying/selling strategies since these averages smooth out random fluctuations. To demonstrate, the quick table calculation example I will be covering will focus on the moving average (MA). We will be building a 50 day MA for the price action of Bitcoin – a very controversial and volatile asset.

Step 1: Download Bitcoin’s historical data from Yahoo Finance. I have selected the time period: September 17, 2014 – October 15, 2022, and frequency: daily. Connect to this dataset in Tableau.

Step 2: Drag and drop [Date] and [Close] onto the column and row shelves respectively, where DAY(Date) is viewed as a continuous date dimension.

At first glance, it seems like the bubble has burst! If you are a crypto enthusiast like myself, then I am sure you are on a shopping frenzy at these prices.

Step 3: Drag and drop [Close] in the column shelf again next to the [Close] measure placed in step 2. Right click this new measure, hover over Quick Table Calculation and select Moving Average.

You will now notice a triangle/delta symbol present in the second SUM(close) pill. This indicates a table calculation has been applied.

Step 4: Edit the table calculation with the following configurations.

By default, “Previous values” is set to 2 and the “Current value” box is ticked. Since we want to achieve a 50 day MA, we will change the previous values to 50. By convention, asset prices do not include the the current period (in our case the current day) for the MA calculation. Hence, we will untick the current value option.

It is best practice to specify which dimension the table calculation is applied. However, the default for selecting Table (across) will produce the same result.

Step 5: Dual and synchronise both graphs so that they are sharing the same axes. Hide the second vertical axis by deselecting “Show Header”.

Step 6: With a bit of formatting, the following figure is achieved which illustrates the daily price action of BTC/USD (orange) and its 50 day MA (blue) over time.

Thank you for reading my second Tableau tutorial!