Disclaimer: This is going to be one of those dates where the other person just constantly talks about work, because I will be going through some date formulas and how to create parameters to filter out different date views.
The formulas we will be using include various date functions. Tableau has a great native feature in the right pane of their calculated field builder which explains each formula quite clearly. You also need to create an excel file which lists 2 years worth of dates, just to give yourself a decent timeframe which looks like the squares below. Now, let’s move straight to the calculations.
For calculations which are structured as “Last Week/ Month/ Quarter/ Year”, their calculations are all structured in a similar manner.
When using datediff() tableau suggests having the more recent date appear first in the formula and it will give you a negative answer. As an example, datediff(‘month’, 2019-02-01, 2019-01-01) will give you -1. The calculations we will use are also all ‘if’ statements which follows this logic.
If the date difference in a specified periods equals -1, then it is considered as the last period.
Looking at the Is Last Week formula above, it is essentially finding if the date difference in ‘weeks’ between the date today, and the given date is -1. The date part between the quotation marks can be interchanged with month, quarter and year if that is the period you are after. These calculations default as a measure (green pill) but you will have to convert it to a dimension in order to use it as a filter.
We see that having just the last month is an easy calculation because you want to set the value to equal -1.
However, if you want the last few periods, you need to add in another condition, joined by AND.
If you want last 3 months, but you don’t have the extra condition, you will see this.
As you can see, all the future dates will also be included. However, with the 2nd restriction, you will only keep it to last month. Following on with the calculation below, it basically looks for the period between 1 and 3 months. If it is, then give it a 1.
So far, we have made calculations for the last full weeks, or last full months, but if you are interested in calculations such as month to date or year to date, the 2nd condition just needs to be testing if the date is less than today’s date, as seen in the Current Calendar Year calculation below. This will return everything from 1st January or the year, up until yesterday’s date.
Now for something a little trickier – (Australian) Financial Year to Date
The financial year to date calculation requires a little more finesse than the other calculations. To follow the calculation for it below, you want to find if the year part of the date (after adding 6 months on to it) is the same year part of today’s date (after adding 6 months on to it). This means that on the 1st July 2018, if you add 6 months on to the date, you will get 1st January 2019, which will have the same year as any date up until 30th June 2019.
Finally, for that added flair, you would want to chuck all these time periods into a parameter to let your end user control what they want to see. To do so, create a parameter with the controls as in the picture below.
Next you would want to put these parameters into a case statement (also shown below) and click and drag the calculation into colour in the marks card.
