Tableau in the office – episode 3

I’ve been revising some old dashboards at work that definitely are due for a refresh. One of the updates is to demonstrate Year over Year for a given metric. I wanted the calculation for current year to compare Jan 1 to date against previous year. But if the user selects to view YoY of previous years, I wanted to show full year difference, not Jan 1 to date. Below was my approach.

For sample data, I created an Excel file with random values per date from 1/1/2011 to 5/31/2015. You can download a copy of it here. To get my desired result, it will take one parameter and three calculated fields in the Filter shelf.

First, I created a Parameter for Year.

ScreenHunter_01 May. 20 16.07

Then I created a formula to only show the year selected in the Parameter and the year before it. This is a boolean expression and I place it on the Filter shelf, selecting the True result.

[Year] = year([Date]) or [Year] – 1 = year([Date])

In my scenario, I only want to show YoY of a year at a time. And since YoY is created with a table calculation for percent difference from previous, I needed a way to keep the result but hide the previous year in the view since it is blank. I used a simple index() calculation and placed this in the Filter shelf, selecting the value “2”.

Next, the following formula creates a calculated field which again is used in the Filter shelf. I’ve broken down the formula with the //comments.

//If a data point’s date is this year and in past months, keep it.
if [Year] = datepart(‘year’, today())
and datepart(‘month’,[Date]) < datepart(‘month’, today())
then ‘keep’
//if a data point’s date is in this year and in current or future months, filter it.
elseif [Year] = datepart(‘year’, today())
and datepart(‘month’,[Date]) > datepart(‘month’, today())
then ‘filter’
//if a data point’s date is in past years, keep it.
elseif [Year] < datepart(‘year’, today())
then ‘keep’
else ‘filter’
end

Place this calculated field in Filter shelf and select “keep”. Here are the three calculated fields in the Filter shelf. Any field I create strictly for filtering, I name with a “f.” prefix so I know its intended purpose.

ScreenHunter_02 May. 21 09.57

Now I place the Units measure in the Text shelf, and change the Sum(Units) to a Percent Difference table calculation.

ScreenHunter_03 May. 21 10.02

I also created a “label” field so the text changes depending on the Year parameter. This is to make it clear to the audience that YoY for current year is a Jan 1 to date comparison over previous year. But previous year to previous year minus 1 comparisons are Full Year over Full Year.

if [Year] = datepart(‘year’,today()) then “YoY” else “FYoFY” END

Add this “label” field to the Text shelf and lastly, add Year(Date) to the Text shelf too. Once all my text fields are in place, I edit the label as follows:

ScreenHunter_06 May. 21 10.09

The final result is below. Change the Parameter to view the differing YoY or FYoFY calculations. I included a table with the sample data.

Thanks for tuning in!

Share Button

5 comments

  1. Thanks for taking the time to post this. This was very helpful in what I am trying to do. I am trying to display the YoY percentage under the grand total and not use the slider/parameter. I am able to generate the YoY using the Table calculation for the “full” years, but when I get to the partial year of 2015, I end up comparing the full 2014 vs. the YTD 2015. Could you point me in the right direction to be able to display the Full YoYs for 2011-2014 and 2015 YTD YoY on the same worksheet? Thanks, in advance, for whatever you can share.

    1. Hi Roger,

      That’s a tricky one. My example above utilizes a date filter. But your scenario, you’d have to create a calculated field with a table calculation to only sum 2014 YTD when comparing to 2015 but 2014 full year when comparing to 2013. That’s how I would approach it. Depending on how your sheet is set up, LOOKUP would likely be a way to do this. I’m sure it’s possible. I’ll try to think about it more and see if I come up with something. Thanks for your comment.

      1. Bernardo:
        Thanks for the reply. That’s exactly how I thought of it, on the conceptual basis. I’ve been scouring (and posted on) the Tableau forums as well. I’ll look into the LOOKUP as well tomorrow. If I find a solution, I’ll let you know! Thanks again.

    1. Fantastic! That’s definitely a great way to accomplish it. There’s always more than one way to do something in Tableau.

Leave a Reply