The Last Function
If you have used relative date filters in Tableau, you will know that they are very powerful. You can create a report which will dynamically update for the past 6 / 12 months, as your data refreshes - very clever stuff really.
The issue that I have found however, is the relative filter will always anchor to today (I know you can manually change the anchor, but as I understand, that then becomes manual as opposed to automated).
I’m rambling, so let me try and explain myself using an example:
- So say I am a data analyst, and I am responsible for a Monthly report that goes out to our senior management team
- The report needs to go out by today (11th August)
- Now as it’s a monthly report - I should only have data up until the end of July in it
- But because my database refreshes daily, my relative date filter in Tableau is bringing back August (part month) information too as so:
So, here's one way that i deal with this ( I'm sure there are other ways), I use the last() function in Tableau/
What is the last() function?
Best explained by the Tableau Online Guide:
Applying this to our scenario:
So if we turn our bar chart into a cross tab, create a calculated field with the syntax last(), and then drag this into the view, this is what we get(note it is a table calc so watch out for what it is computing using - i.e across, down,etc):
So we know that the latest date is the value 0 in the last function.
So if we now create a binary calculation as follows:
This calculation will bring back 'True' whenever the Date is the latest month, and false otherwise, which actually works great because we will only want to show August data once the month is complete and we are in September.
So drag this calculation into the filter window, and select False (note, you still keep your relative date filter in too, so show last X months worth of data):
And voila, you're done!