Why Vizioneer?

My photo
Atlanta, Georgia, United States
The "Vizioneer" comes from mashing two words that have shaped my world for most of my adult life - Engineer and [data] Visualizations (or Vizes to those who know what's up). Graduating from first from Oglethorpe University in Atlanta, followed by Georgia Tech with my Bachelors and Masters in Civil Engineering, all of which taught me to think through anything and everything - problem solving, "engineering" solutions, teaching to the "ah ha" moments - is what I love to do. In 2010 that investigative, engineering mindset intersected a job change and a plunge into the world of Data Analysis. In the search for the next great thing I stumbled on to a data visualization and dashboarding product called Tableau software and things just took off. So now I guess you could call me that engineer with the sweet data visualizations - or just "The Vizioneer" :)

In 2013, I joined the incredible team at Slalom, focusing on Tableau and it's been an amazing experience. Recently in 2014, I was honored and humbled to receive Tableau's highest recognition of being named a Tableau Zen Master. Follow along to see what happens next :)

Monday, April 14, 2014

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 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

3 comments:

  1. Hi Nelson! I Tried adding the parameters to my Dashboards but not sure how? I can add them to a sheet but doesn't appear to filter? Are you able to show an example screen shot, please? Loving your blog!

    ReplyDelete
  2. Honor -
    Thanks for checking out the blog! I'm curious if you're asking about how to make a parameter in general, or is your question more to how to make a parameter work as a filter through a calculated field? Let me know and we'll get it figured out. Many thanks!

    ReplyDelete
  3. Hi,
    I am trying to include same feature where I have dashboard with worksheets that source date from multiple databases and tables. All the table have date fields in same format but column names are different. I am trying to filter the data based on common date.
    For example, If I select "Today" from filter then the dashboard should show data from all worksheets based on that filter.
    I am trying to use parameters like you have suggested but I am still struggling. Since this was an old post, I am wondering if this is still applicable to newer versions of Tableau?
    Thanks !

    ReplyDelete