Following on from my previous Tableau post... For business reporting, you are often wanting to do timeframe comparisons. Eg, "How is the current month performing compared to the previous month?" Or compared with the same month last year.
This could be in terms of sales, expenses, you name it.
Setting this capacity up in Tableau isn't as straight forward as you might think. And lately I have been wondering about what is best practice for this.
So here is my process, for generating the following simple dashboard.
And up on Tableau Public:
How's it done?
Generating the required calculated fields for this dashboard is not so simple.
Here they all are:
Fortunately, by using folders, we are able to keep them out of sight.
The key component to creating these measures are two parameters:
The first specifies the reference date (i.e the date from which you are wanting to make comparisons).
Generally we would want to use either today's date or the max date in our database (usually yesterday, if it refreshes overnight). But importantly, you will often want to run an "end of the month" analysis. So you need to flexibility to specify that date.
A new feature for parameters is that it can refresh a parameter's list whenever the workbook is opened (both for local and server versions). What this means is that all unique dates from the [date] column will be brought in.
This gives us a slider, that, unfortunately, goes latest from the left. And doesn't give us a date picker
If we absolutely need the date picker, we would need to use a slightly different parameter.
We continue with the former but swapping to the latter wouldn't be too much of an effort.
The reference date that we use is controlled by the above parameter selections and the following formula.
This add that specific date as a column in our data source. And then all our relevant start dates and end dates (of our periods) are calculated off that.
Then those start and end dates are used to make a series of measures where the sales value is only shown, if the date for that row is between a specified range. Like so:
There are a number of these:
And I used the following chart to test that they were all working, as expected.
How can I use this?
You have your own data source loaded in a different tableau workbook. You can download my workbook right here. The next step is to copy over one sheet and it will bring with it all the parameters and calculated fields. Then you just need to replace the data source with your own and update the references for my [date] and [sales] columns, if yours are different. Like so (yes, my computer is a bit slow):
This is a bit more painful. You can copy and paste each of the formulas over. Each of them is included in the gist below:
The trick is to open a new calculated field. Make it big and then paste in a portion of the formulas and then drag each over to the dimensions/measures pane (including the comment -which will become their title).
I have noticed that, sometimes, I get a red cross for the first formula (you see it in the gif below). This is not so much of a problem, just copy over the rest, remove the others and label the current calculation as that formula's name.
I don't do all of them in this video, but you get the picture.
So there it is. This measures will update as your sources get more data. And you now have the freedom to also do lots of interesting calculations, such as percent difference. And you can even start working out simple forecasts for the remainder of the current period but I think I will keep those advanced methods for my paying customers :-)
Hope you found this post useful.