I turned 30 this April which means I’m now old… or young, depending your perspective (mine vs. yours). Most normal people would celebrate such an occasion by doing something that reminds them they’re still young to mark the time – jumping out of a perfectly good airplane and trusting your life to a parachute, wrestling lucha libre style against a Jack Black lookalike, or visiting all 30 MLB stadiums in one summer. Those people are much smarter than me. Instead, I combined a number of things I love – Tableau (of course), teaching, hacking, innovation, and sharing ideas – to come up with what I called the #Tableau30for30. Blogging on a Tableau hack or trick each day, for 30 days, in the month I turned 30. And I thought the skydivers were crazy.
At this point we’re well over halfway and the end is in sight. I’ve learned a ton – not just about Tableau, but also about blogging, social media, and branding. My blog site, TheVizioneer (a mash up of the words “Visualization” or “Viz” for short and “Engineer” which comes from my formal education in Civil Engineering, which I believe helps with out of the box problem solving), has had over 5000 visitors this April alone, and the feedback from the Tableau Community on Twitter has also been fantastic. The coolest part was that, without my knowledge, Tableau was also planning to do “Tableau Tips Month” in the month of April (I love it when the stars align!) and their good folks asked if we could team up. So I’m honored to share with you the trick from Day 14 of the #Tableau30for30 - Filter Multiple Dates with Parameters. I hope you all enjoy this tip and would love to have you follow along as we wrap up the remainder of #Tableau30for30. Many thanks!
Day 14: Filter Multiple Dates with Parameters
Today, April 14th is a big date in the Davis family. Seven years ago we walked down the aisle and made it official. She became my better half and I got to continue to tell her nerdy jokes that she still pretends are funny (bless her heart). (And everyone said 'awwww').
Dates. Today's a big one for us. I'm sure you have some big ones too. I also bet the same thing can be said of your data (see that transition???). I love looking at trends over time and some of the filtering that you can do in Tableau is pretty awesome. Right up to the point where you want to be able to filter multiple data sources based on date filter. That becomes either very complicated or impossible based on your skill level. Today we're going to look at how you can still give the user almost complete control over date filtering even when your dates are coming from multiple sources.
I'll give you the actual set up of where I had to come up with this one. I've mentioned before that my first assignment after joining Slalom was to create a demo dashboard based on financial data. I created about seven or eight dashboards that all tied together and there were multiple data sources, so filtering the data based on date from just one source was inadequate. I needed a way for the user to set their date filter and have the entire workbook update. I knew the only way to do it was with a parameter, but how would I tie it all back together? This was a bit of an advanced move, but three parameters and two calculated fields later, I was cooking with hot oil. So who's ready? Let's dig in.
We're going to make some assumptions:
- You have multiple data sets
- They all have dates that relate the time periods to each other
- That when I show you what to do with one of these data sets/sheets and you can iterate it as many times as needed (ie in each of your data sets).
Let me explain what the end product is going to be and we'll work backward. We're going to create a functional version of what you see below (which practically would show the four quarters of 2013):
Above you see a date filter where the user inputs three things (each one is a parameter, going in order left to right):
- The first is the number of time periods. An integer that must be greater than 0.
- What the time period unit actually is - weeks, months, quarters or years. This is going to come from a string.
- What the time period cut off is - I chose to give the ability to cut off at the end of each quarter, or to have no cut off and use "As of Today" as an option. I chose to do this as a string but it's probably possible do it as a date parameter as well.
So let's make each one. First, the number of time periods - it's just an integer that has to be greater than 1, like this:
Ok, next - the different units of time. We do this as a string with the time periods written out (lower case, in the singular form), like this:
Here's the last one, the cut off date. I've done this as a string, but it's probably doable as a date type (the issue I run in to is passing in Today() in here as a date - not sure that it's doable using this as a date type parameter, thus I go with the string type). Here's what it looks like (I show both YY and YYYY to show that they both work and because I'm a goofball):
So now all of our parameters are made. We now have to create two very important calculated fields. And here's the thing - both of these field must reside in ALL of you datasets that have dates. That's right ALL of them. Probably a good time to mention that Day 57 of #Tableau30for30 would for have been on Copying Calculated fields from one data source to another, but alas there's only 30 days in April (thank the Lord). So I'll let you figure that one out.
The first calculated field is a case statement that switches that cutoff date string parameter to a date type. Here's what it looks like:
The other calculated field is a boolean that brings everything together. I wish I could say that this was really simple, but the first time I wrote it out and got it working, it took a couple hours to get just right. The idea is to show everything going back to an N number of time periods through the cutoff date. I could try to explain the whole thing, but that would take a while and you would feel like I was insulting your intelligence to think you couldn't see this and understand it. And the last thing I'd ever want to do is insult the intelligence of a blog reader. I like you guys. And your friends... who you just told to subscribe... Oh, yeah, back to the calculated field ([Time Period] is my date field from my data):
Now you got it! And again, this has to go in to every data source (and needs to be modified if the name of your date field changes). This calculated field, the one I called "Date Filter - Global" also needs to be added to EVERY SINGLE ONE of your date sensitive sheets in the filter shelf (filter=true) AND added to context EVERY SINGLE TIME.
Is that a pain? Sorta, but you needed a way to filter dates, and once this works it's beyond awesome.
Lastly, you'll need to include those three parameters in each of your dashboards that are going to leverage this solution (assuming you want the user to have the ability to change the range as they look at that particular dashboard). To end where we began, this is how mine looks:
I hope that helps. If so, drop me a line and let me know. See you tomorrow, we'll be half way home. Many thanks!