Date and time functions

1. Date and time functions

Welcome back! In this chapter we will work with time series analysis so let's start by learning how to work with dates in Tableau.

2. What is time series analysis?

Let's begin with defining time series analysis. It is a study of data in particular periods or intervals. It may involve trend analysis, forecasting, and comparisons of data across various seasons and rolling time periods. We will study various types of time series analyses and underlying calculations in this chapter.

3. Date and time as data types

Before we do so, let's examine the critical component of such calculations: the Date or DateTime field. These data types store calendar date and time information and, in the vast majority of cases, are auto-recognized by Tableau as dates based on standard date and time formats. These, however, may strictly depend on your data locale, so language and region settings, defining standard formats of date and time but also of decimal and thousand separators and so on. If your fields do not get auto-recognized as dates, it might be worth checking the date locale. We will see it in the demo. In Tableau, date and time data types are represented with a little calendar, in the time variant, it is accompanied by a little clock icon. Important to notice that a field recognized as a date may have various output formats, so always make sure that you are dealing with a real date field and not a string-type look-alike.

4. Deriving Date(Time) from other columns

What if we do not have a date field in our dataset, but we have the columns where we can derive the date from? Tableau has three handy functions to do that. The first two, MakeDate and MakeTime, come in handy when we want to assemble a date and time from existing numeric columns. MakeDateTime combines both a Date field and a Time field into a timestamp.

5. Parsing dates

Parsing, or in other words, analyzing a string into logical syntactic components, comes in handy when dealing with more specific DateTime formats. DateParse converts any string field into a date based on the input format, which we can adapt according to standard date and time components such as day, hour, millisecond, etc.

6. Extracting date parts

Tableau also has handy functions that allow us to extract parts of the date or truncate the date. The DatePart function extracts year, month, day or even day of the year, weekday, and a few other possible day parts from an existing date field. It happens each time when you drop a discrete date onto columns or rows on the canvas.

7. Truncating dates

The DateTrunc truncates, or, in other words, cuts the date to the specified date defined by you; for example, if we truncate 15th June of 2022 to month accuracy, it will return the first day of June of 2022 or to Q2 of 2022.

8. Calculating with dates

Working with dates, we might often need to add or subtract time from the dates. The DateAdd is a function that allows us to add a specified amount of intervals, so for example days, weeks, and months, but also hours and minutes, to any date or DateTime field. Note that the result will be a date field. We can also subtract date or time intervals, using the Datediff function and specifying the requested interval, the start, and the end date. Note that the result will be a number.

9. Referring to a date in a calculation

Before we head off to the demo and the exercises, it would be handy to know how we can refer to concrete dates in any calculation. To do that, we enclose the date between two hash signs. For example, if we wanted to limit the calculation of sum of sales in this table to sales as of 2011, we could use this calculation, referring to the 31st of December 2010 explicitly.

10. Let's practice!

Enough theory! Let's see it all in practice.

Create Your Free Account

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