Last week at the Tableau Customer Conference (TCC12) I picked up a few new tricks for my toolbox. One of these, Dynamic Display, solves a problem I struggled with earlier this year: how to show year over year change without showing the previous year (and particularly without showing an empty previous year).
If you produce period over period dashboards you’ve probably run into this yourself. The common recommended fix has been to Hide the previous year, which doesn’t work for all scenarios and is a lot of extra coding.
In the past when building year-over-year dashboards I would put two years on the worksheet, add my % Change from Previous Year calculated field and end up with something that looked like this:
If you’ve been here you know the next step is to Hide the last period, or 2011 in this case.
That works fine if you’re presenting a static view but grossly underutilizes Tableau’s data exploration features. What if I next want to compare 2011 to 2010? I’d need to add a Quick Filter to my view and then change the dates. But wait, I already Hid 2011. My next view is even less informative than the last one – it’s just a bunch of nulls:
Now, using a Parameter and a couple of Calculated Fields you can stop hiding the last year and let you user dynamically select which periods to display. Here’s what I’d really wanted to display but been unable to until now:
You can download the data yourself from San Francisco Data or use the extract included with the Tableau workbook.
Here’s how to construct this view yourself:
- Put Opened (Date) on the Columns Shelf and show discrete year and month values.
- Put Cases on the Rows Shelf.
- For Cases, add Quick Table Calculation > Year over Year Growth. (I saved this as a Calculated Field called “% Diff Cases YoY” in my workbook.)
- For my viz I did a couple of extra things that are unnecessary for this tutorial but if you want yours to be the same:- Add Category (group) to the Filters Mark and Filter on Top 3 by Cases.- Add sum(Cases) to the Size Mark.- Add Category (group) to the Colors Mark.
- Create a Parameter called “Year”. The parameter should contain the years that you want to display in comparison to previous years. Be sure to omit the first year to avoid null values for your initial Year over Year Growth calculation.
- Right Click on the Year parameter and select Show Parameter Control. Choose the display type you’d like. I prefer Slider for this view.
- Next create a Calculated Field called “Year Filter”. The filter will be a boolean evaluation, which is much faster in Tableau than an IF/THEN statement. The body of the field should be:
year([Opened]) = [Year] or year([Opened]) = [Year] -1
- Add the Year dimension you just created to your Filters Mark and select “True”. The viz will update to display only 2012 and 2011. (If you choose a different Default Value for your Year parameter it will display that Year and the previous year.)
- Create a second Calculated Field called “Index”. The formula is merely Index().
- Before clicking OK, click the “Default Table Calculation” link on the right. This is important because the default calculation is Table Across and you want to set it for Year of Opened. Otherwise you’re going to get Month over Month for your Index and Year over Year for your values and that’s a mashup you want to avoid.
- Set the Default Table Calculation to Compute using: Opened (your date field)At the level of: Year of Opened (Year since you want a Year over Year Index)Restarting every: None
- Finally, drag Index onto the Filters Mark. The only options should be 1 and 2. Select 2. If you see more than that revisit your Table Calculation definition. It’s likely that your computation is not at the right level. This is because you’ve already added your Year filter and there are only two years in play, so your indexing should not exceed two.
Once you’ve added Index to the Filters Mark the view will display only one year, 2012 or whichever integer you specified in your Default Value was for the Year parameter.
While playing with the Year parameter, I noticed that each year Graffiti seemed to have a precipituous drop in April or May from the previous year. Often alternating. To give more context to the viz, I added a view for Cases by month to illustrate that year’s trend. Below is an alternate view I constructed. Please share yours if you have another.
All the Tableau dashboards shown in this post are included in the same workbook. Click any of the Tableau Download links embedded in the image to get a copy for yourself and view the source code.