Time series analysis

1. Time series analysis

Welcome back. In this video, we will build on our date and time calculation skills and progress to the more advanced time series analysis.

2. Seasonality and year-over-year analysis

Let's start with the concept of seasonality. This analysis studies repetitive patterns in data to detect predictable trends, identify seasonal highs and lows and improve overall forecasting decisions. Central to the concept of seasonality is year-over-year analysis, which sets side-by-side various discrete DateTime intervals, for example, months, and checks measure's performance for these intervals across the years. Looking at this year-over-year chart, presenting visits to the cinema, we can detect a seasonal drop in the months of June and September, probably related to good weather and the start of the school season.

3. Year-to-date and custom fiscal years

Another handy and often used calculation is the Year-to-date Quick Table Calculation. In essence, it's a preformatted running calculation (for example, sum) restarting each year. It can be customized as month- or quarter-to-date by adapting the Table Calculation accordingly. In case we need to work with custom calendars, for example, a specific fiscal year, Tableau has a built-in option allowing us to set it up without the need for any additional mapping tables. It is, therefore, possible to work with various sets of calendars in one workbook.

4. Calculating growth

Calculating growth, for example, Year over Year growth per quarter is possible with a ready Quick Table Calculation. Since it compares to the same period last year, it requires discrete date dimensions. The calculation behind it relies on a LOOKUP function with a defined offset value, in our case, one previous quarter, so -1. Since we compare in the vertical direction, it is a Table (down) calculation.

5. Compound Annual Growth Rate - CAGR

Analysts working with any financial data often calculate the Compound Annual Growth Rate, CAGR in short. It measures the average annualized growth of a metric across a period typically longer than one year. In essence, it provides one aggregated number that defines the growth for the entire measurement period. For example, looking at 2021 Sales of North Yorkshire, we observe a 32% average compound annual growth rate over the previous three years, indicated on the chart with the red line, mainly driven by the spike in the last year. CAGR is calculated by dividing the final period value by the first period and raising the result to an exponent of one divided by the number of years and subtracting one from it. The related Tableau formula may look scary, but luckily, there is a Quick Table Calculation to start with, so adapting it to our needs is easy.

6. Moving (rolling) calculations

Next up are the moving or rolling calculations. They aggregate on a specified number of values before and/or after the point in time and are mainly used to smoothen the fluctuations of highly granular data and to disclose the long-term trends, "the big picture". Imagine we wanted to analyze the Daily Sales across a three-year horizon. With many data points, it's hard to see any trends.

7. Moving (rolling) calculations

Calculating a moving average of, for example, 180 days immediately exposes the underlying, increasing trend in Sales. In Tableau, we are using window functions to achieve this smoothing, and the good news is that moving calculations are also included in Quick Table Calculations to get you started in no time.

8. Keeping it organized

Finally, some tips and tricks on how to keep your complex calculations understandable for the end-user. Consider combining various related time-series visualizations into a dashboard. Using parameters, highlighters, and hiding extra relevant information in a tooltip can make it interactive without losing sight of important details. Remember also to use dual axis sparingly and clearly annotate both axes.

9. Let's practice!

Let's see how much you remember - time for the knowledge check.

Create Your Free Account

By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.