OK time for another little Tableau tip which might come in useful. This is one I now use all the time. The idea of using an index to hide time based values was first brought to my attention about 18 months ago by Tom Brown of The Information Lab (if I remember correctly - there is a chance it was Craig Bloodworth or Andy Cotgreave but I'm pretty sure it was Tom).
The situation is this: You've got a time series based table calculation going on that you are interested in showing, something like a running total, YTD total or difference from last month, however you don't want to show all the data - instead you only want to show the latest month.
Your starting position might look something like this below, with year, month, sum of sales, monthly difference in sales and running total.
Now lets say you only want to show the latest month, in this case December 2013. Well the first thing that springs to mind might be to filter, but that messes with the calculations PLUS its not going to automatically update to the latest month when the data updates:
Now the next idea you have might be to HIDE the 'non latest month' data, and that will solve the issue of the filters messing up the table calculations, but its still going to leave you with a problem when you get a new months worth of data you want to automatically show. So this is what you can do....
First create an index field:
now make this discrete
then pop the index field into your viz just after the 'month' pill
now comes the clever bit. To show just the latest month you want to hide everything else. To do this you need to make the Index value along side the latest month be equal to something unique and that will stay constant as the data updates, and then hide everything else. One way to do this is to sort year and month descending, and then highlight all the index fields that are not 1 and click hide. This can work fine for calculations of difference along time BUT does not work for running sums as it flips the year around to calculate from December running to January. So the safe bet is to take the following step:
Create another calculated field that looks like this
this creates a Boolean True/False variable that you can now add to your view and hide the Falses:
then unclick 'Show Header' for both 'index' and 'index match'
and you are left with the view you want, which will automatically update to the latest month as your data refreshes:
And then you can do what you want with that view, change the aliases, add it to a dashboard etc....
If anyone has an alternative approach to this I would love to hear what it is, as with so many things in Tableau there are probably multiple ways to achieve the same goal.
