Category Archives: Year over Year

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’

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

Year over Year Quick Tip

Over the years as a Tableau user I’ve learned to adjust my vizzes depending on the audience. Displaying Year over Year is an important metric to many at my organization. Here is a quick and easy way that I use often.

I’m using the Superstore sample data that ships with Tableau. Place Order Date in the Columns shelf and Sales in the Rows shelf, change the Marks to Bar.

ScreenHunter_06 Feb. 01 20.01

Now grab Sales again and place it in the Label Marks. Hover over the right edge of the pill until you see the triangle. Click it and move down to Quick Table Calculation > Percent Difference

ScreenHunter_09 Feb. 01 20.05

And that’s it! Tableau will use the earliest date as the base line and calculate the YoY change.

ScreenHunter_10 Feb. 01 20.06

I like this view because the viz utilizes the Y axis to display the Sales in dollars, but the label shows the YoY growth. The two metrics combined provide more context to your audience.

One thing to keep in mind is that if current year is not over, filter for months. Let’s say today is currently June 2013 and management wants to know how Sales are tracking against previous years.

ScreenHunter_12 Feb. 01 20.09

Hold right click as you drag Order Date into the Filter shelf. Filter on Months and only include January to May. Very simple!

Share Button