1. Reforming dates in practice
In this demo, we will be visiting the Tableau superstore dataset where we'll demonstrate how we can make use of date time functions to reformat data.
Observing our data on the left, we have a column called Order ID Compound Key.
This column here is made up of the State, Year, Date, Customer ID, and Product Code.
Dragging this in - we’re interested in the Order Date which is the 3rd token in this string.
Let’s first extract the date of interest here, we’ll call this Cust Order Date.
Using the split command, we’ll note that we want the dash delimiter and the 3rd token.
We’ll drag this to our Canvas and here’s the list of dates.
Now we’ve got some dates - but it’s clear they aren’t recognized as dates just yet.
Can we just click Convert to Date?
Let’s see what happens.
It doesn’t look like our date has been converted correctly - unless orders were accepted in the 1400’s! Let’s undo this and use the ISDATE() function to help us out.
The ISDATE() function accepts a string, so we’ll drag in our Cust Order Date and name this is_date_valid.
Let’s drag this into our canvas.
We can see that the ISDATE() field doesn’t believe these are valid dates.
That’s fair because the format appears to be Month-Year-Date which is a format Tableau isn’t expecting.
So, what can we do when the date commands fail us here?
Well, we can use the DATEPARSE() function.
DATEPARSE() can interpret any date format as long as we match the format exactly.
First, let's showcase why capitalization matters for our DATEPARSE() string.
We’ve created two fields Date_Parse_Minute and Date_Parse_Month, where we’ll show the difference with a visualization.
Firstly, let’s take a look at the Date_Parse_Minute field.
So far, it looks fine in our canvas.
Now let’s change Sales to Week Number and - something is clearly wrong here.
Let’s now look at Date_Parse Month.
We’ll change it to Week Number, and things are looking good!
Did you spot the difference? You would note that for the minute field, it is adding a minute for each date locked to January, whereas the month field correctly reflects the date with no minute additions.
So remember, lowercase m is minutes, and uppercase M is months.
.
Now with our date corrected, let’s finally use this to analyze our data briefly!
Let’s adjust the granularity to a weekly granularity first, and then we’ll drag in our Sales.
With our cleaned data, we can now see some odd peaks that might have seasonal elements at play here.
We’ll modify our Sales to a cumulative total, but - it’s not clear whether seasonality is at play here.
We’ll drag in a field we prepared earlier, seasonality, into our color, and there we have it.
We can clearly see that Fall has a much more rapid step rate of change.
2. Let's practice!
Now it's over to you.