One of my pet peeves in Tableau is that you cannot have Dynamic Parameters, that is parameters that can be set to a calculation to determine its value at runtime. A common requirement here I get is to have a dynamic date range (e.g. Using the last whole 12 Months, so if today is 15 March 2016, then the range needs to be 1 March 2015 - 29 Feb 2016).
Standard Tableau Approach:
A standard tableau approach (for the above range) would be to use a relative date filer and set to the last 13 months, but that will also include this month data.
So, to exclude this months data, we can create a calculated measure that checks if the date dimension is in the current month, then filter when it is:
BUT! This is fine if the date range is static and always the :Last full 12 months". What if you want to have your dashboard default to the last 12 months as a start, then allow your users to change the date range to suite their needs? Then you'll have to show both filters and explain to the users how the combined approach works. Not very user intuitive.
In my hack approach, I add two parameters to the tableau workbook ([Start Date] and [End Date]), use that to filter the data, and deploy it to Tableau Server.
I then have a scheduled PowerShell script that uses the Tableau tabcmd command line utility. At a high level I:
This allows for a default date range, that is updated on a schedule (monthly in my case, but can be daily, weekly, etc), plus allows the users to change the date range interactively to suite their needs.
Here is the PowerShell script I used (number are for referencing purposes):
Breakdown of the code:
Lines 1-10 i declare my variables. This includes:
And that's it. I then schedule the script to run on a monthly basis and the workbook is updated every month with the new End Date.
How to determine the path of the workbook on the server?
Navigate to the workbook on tableau server and hover your mouse over the "Download" link. You'll see the path in the status bar at the lower left (in Google Chrome)
Tabcmd Publishing Gotcha
When publishing with tabcmd, by default all the dashboards and views are visible (regardless of how it was previously published using tableau desktop). So if you want to only have the dashboards available for you users, and not the views, then you'll need to hide the views in your Tableau Workbook (right click, Hide Sheet):
Business Intelligence Lead.