This week I’m doing something different while it stays fresh in my mind. If I hadn’t deviated from this #TakeapartTuesday plan, I’d have referred to this Pareto chart guide and banged one of those out.
So what supersedes the unstoppable juggernaut that is #MakeoverMonday? A practical solution to a real-life work problem. The source of that problem was likely to be a poor initial implementation by me, but I learned something new today when solving the issue, so I thought I’d share it.
The gist is this – you’re a sales manager. You have a daily subscription to a Tableau dashboard scheduled for 8am every day, which shows you who has been selling what, when and where. Sounds good? It does – but the dashboard was published a couple of months ago and the image of the viz in your subscription email shows an old cached image from months ago. It’s misleading and the sales manager keeps assuming that “the data has broken”.
As an end user it’s also annoying to have to change the date parameter in the dashboard when you click through to the dashboard, in order to set it to the current month. Isn’t the solution just to filter the view to default to the current month? Almost – but the end user wants to be able to override the current month so they can refer back to performance in prior periods too.
Two parameters and a Set.
- Parameter number one. This is a toggle which will be set to ‘Current month’ when the dashboard is published to ensure that the Sales Managers always have the latest view of data when they click through their subscription.
- Parameter number two. A monthly stepped Order Date Parameter (in the world of Sample – EU Superstore). This will be used as a secondary input by end users where they want to switch away from the ‘Current month’.
- The Set. Here’s where the (simple) *MAGIC* happens:
The Condition formula is easy enough. If the first parameter is set to ‘Current month’, then it’s going to truncate the system date to the month level and then retrieve Order Dates which match when truncated on the same basis.
DATETRUNC() as per the Tableau online help):
Truncates the specified date to the accuracy specified by the date_part. This function returns a new date. For example, when you truncate a date that is in the middle of the month at the month level, this function returns the first day of the month.
Where the [Date anchor] parameter is not ‘Current month’ (i.e. it will be ‘User defined’), then the DATETRUNC() becomes dynamic by referring to the second [Order Date Parameter].
It’s effective, but still has limits. The end user needs to understand that in order to have control over the dashboard, then the first parameter must be set to ‘User defined’ in order for the Condition formula in the Set to be triggered. If it isn’t, then no amount of changing the slider on the secondary parameter will make a difference.
The downloadable workbook showing this technique is available on Tableau Public here. I’m not sure if this is an inelegant solution to a problem which has been better resolved elsewhere, but it worked for me and I hope it can help one or two others as well.