1. Date and time in practice
We will continue to work with the sales and orders dataset as we explore the date and time functions in practice. But first, let’s discuss the concept of data locale.
Our orders dataset contains only string data types, which you can recognize by the ABC icon.
Let’s drop Order Date and Profit to the canvas.
To the human eye, we are looking at a timestamp and numbers; Tableau is still interpreting these as strings of characters. For example, we can’t get the sum of Profit as you cannot use a sum function on strings.
Let’s check the Data source, and try to convert Profit to a numeric data type;
Okay, that didn’t work.
This has to do with data locale, or in other words, the set of parameters that defines the dataset’s language and region. Since we're working with UK datasets, the default decimal delimiter is typically a dot. The Orders dot csv file has been prepared by our colleagues in France, so the file has a different data locale. We can quickly adapt it by clicking on Text file properties and adapting the file locale to France.
With this one handy tweak, suddenly, all the data fields have a correct data type, auto-detected by Tableau.
The good news is that generally, Tableau has our back - with its powerful data type interpretation methods, in the vast majority of cases, it auto-recognizes the fields.
When it doesn’t work, the data locale may be the culprit.
If we don’t like the output of the Order date field and would prefer to look at dates in the US format, so Month, Day, Year, instead, we can apply workbook locale to it.
Let’s click on Workbook locale and select English (United States). Now we have all relevant fields in the correct data types, we can clean the canvas and build a heatmap of Profits. We’ll drop the Month to the Rows and the Weekday to the Columns; very easy! Let’s double-click to look under the hood of these dimensions.
To create a weekday, Tableau uses the DATEPART function on Order Date, referring to the date part ‘weekday’.
The same happens with the Month of Order Date.
Let’s visualize our data as a heatmap, we will drop the summed profit to the canvas and onto Color, and we will change the visualization to square.
Very nice, but are we sure we are presenting a fair comparison for all the months and weekdays?
We'll drop the order date onto Filter to have a quick peek at available dates; hmm, it seems like the timeline covers an uneven amount of months per year.
For example, we have two Junes but only one September between these dates.
Let’s limit our calculations to one full year between June 2017 and May 2018. We could do it in a filter but suppose we only want to apply this treatment to this one calculation.
Now, create a new calculated field.
We will call it “12-month profit”, and type in our condition.
Note that the date between hashes always has to be in Year-Month-Day format, regardless of your workbook locale setup.
Let’s replace the sum of Profit with the new measure, and here we have a much fairer comparison and clear visual proof of Black Friday’s success.
With the knowledge of key date functions as presented in the previous video as well as a good understanding of data locale, we are now ready to use dates in calculations and rock the exercises. Let’s go!
2. Let's practice!