1. Date transformations and visualizations
Welcome back. In this chapter, we'll be covering the most common date reformatting functions you'll use for time series data before diving into the inputs for these functions and common time series visualizations.
2. Date time specific data types
When dealing with time series data, it's important to realize that Tableau treats time series data differently from other data types. Rather than having one date time data type, Tableau returns time series as either date or date-time specific fields.
As we can see from the image, date-time specific fields retain a time element, whilst date fields do not include any time element. This can be seen from the calendar icon for date fields and the calendar and clock for date time fields.
Now Tableau, by default uses color based markers to distinguish between discrete and continuous data fields, but these are interchangeable based off our classification criteria.
3. Date time data models
Now depending on the data you're working with, you might have time series information returned in a date format where the start day, month, year, etc. are all stored separately.
This is known as a date dimension format.
To deal with this in Tableau, you'll need to use the MAKEDATE function to reconstruct such a date.
4. Making dates in Tableau
Makedate and make time are both date formatting functions that help us reformat our data.
Makedate reconstructs dates using the year, month, and day.
However, it lacks any inbuilt date validation for invalid dates such as the 32nd of september, 3000.
5. Making time in Tableau
Now provided you need a time component in your date field, you'll need to use MAKEDATETIME.
A useful way to remember this is simple:
If there's time involved use MAKEDATETIME.
If there's only the date involved - MAKEDATE will be sufficient.
6. Date truncation in Tableau
Now DATETRUNC is another useful function that returns a date field to the desired specificity.
For example, if we're interested in truncating the dates to a weekly basis, DATETRUNC will return a set of dates starting at the start of each week.
This can be particularly useful when you're seeking to perform cumulative visualizations and need to reformat your time series data accordingly.
7. Calculations between date timestamps
Now tableau, similar to Excel, lets you go ahead and subtract one date from another.
However, unlike Excel which would preserve the absolute difference between two time stamps, Tableau doesn't.
Tableau automatically rounds up your results which can be dangerous.
DATEDIFF returns the difference between two time periods, with rounding specified by the granularity requested.
Best practice would dictate us to return the results at the lowest level of granularity we need for our analysis.
For example, if I want the fractional days between two time periods, I'd need to use DATEDIFF and specify the minute granularity and divide this by 60, to get my hours, and then by 24, to get the number of days elapsed in fractional format.
If I did this via Date Field One - Date Field Two, this would return the number of days, but would lose the fractional form as it would round down.
8. Visualizations with time series
Now that we've talked about our date time functions, let's look at another equally important aspect, data visualizations.
As you analyze time series data, there are many graphics you can use to see how the data changes. Here, we'll highlight four common visuals that you'll be utilizing.
Stacked Bar Charts are an effective way to represent how quantities change over time. This is more commonly associated with discrete data quantities as opposed to continuous.
Area Charts on the other hand, are useful to showcase the rise and fall of various data series over time where the troughs and peaks are emphasized to highlight rates of change.
Now what might happen if you want to showcase compound growth over time? You can make use of cumulative visual as a way of showing compound growth. We've used a column chart, but you can interchangeably use line charts.
Lastly, line charts provide an effective way to visualize changes in a data series over time for both short and long time series where this is useful across both univariate and multivariate analysis.
9. Let's practice!
Now onto you. Let's practice!