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.

Learn About Tableau

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 a 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
- I'll show you what to do on 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 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 fixable 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 cut off date string parameter to a date type.  Here's what it looks like:

case [As of Date Param]
when "Today()" then Today()
else DATE( [As of Date Param] )
end

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 cut off 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):

DATETRUNC([Time Period - Last n Unit], 
dateadd([Time Period - Last n Unit],(-1*[Time Period - Last n Number]+1),
datetrunc([Time Period - Last n Unit], [As of Date] )) )<=[Time Period]

and [As of Date]>=[Time Period]

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 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.  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!
Nelson