Last 6 Months of Data
A common request is to show only the last 6 months of data, to get the impression of the trends of data over the most recent period of time. For data that is up to date then this is not a big problem. After all tableau does provide a lovely out-of-the-box relative date filter that can show you the last six months from either the current date or a specific fixed date. (Watch this space for a future blog post on Tableaus date filters)
Too much data!
Recently however I came across a subtle variation of this problem, and one that adds another layer of complexity to the situation. In this particular case the client wanted the last six months of data, however they were going to be updating the dashboard in such a way that the data would always be between one and two months in arrears. So each month the filter would need to be edited to precisely show six months worth of data and I wanted to build the dashboard so that I wouldn't have to do this each month, as there were dozens of views which needed this relative date filter. So after a little thought I came up with the following solution.
I would use a boolean calculated field to highlight those records in the dataset where the date is within the last 6 months and use a date parameter to specify the date which you would like the last 6 months to be relative to.
So without further delay, here is the formula;
DATEDIFF(‘month’,[Date],[Date Parameter])<6 AND ([Date]<=[Date Parameter])
Lets break this down. There are two components to this formula, the second is relatively simple this part: ([Date]<=[Date Parameter]) checks that the date is in the past relative to the date selected in the parameter, the reasoning for this is that the data may be partially updated over the month, and the user would only want to change the dashboard once all the data had been entered. This also allows the user to view versions of the report from any point in the history of the data.
The first part is where the magic happens; DATEDIFF(‘month’,[Date],[Date Parameter])<6 here the calculation looks at the data for that row and if the month part of the date is less than six months different from the date selected by the user it returns true. The reason why its less than 6 and not less than or equal to 6 is that the month that the user has selected is included in the 6 month period, if it was not or no data was going to exist for the month the user has selected then you could use less than or equal to instead.
So the formula returns true for dates that are within 6 months of the date selected and in the past relative to the date selected. To use this simply place the new calculated field on the filters shelf and select true.
Hope this #TableauTopTip has been useful as always we always appreciate any feedback you might have.