Category Archives: YoY

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

Beer!

Beer is awesome! What else is new, right? I’ve been thinking about doing a beer post and started looking around the internet for data. If you’re a fan of beer, you’ve noticed the rise in new brewers over the last decade. In fact, Denizens Brewing Co. just opened across the street from my office. I’ve heard rumors our engineering team is looking at ways to boost our wifi signal, allegedly of course. The explosion of craft beer has been great for beer nerds like myself. I love trying a new beer with friends and I keep finding new favorites. I prefer the IPAs but will try just about anything.

Off to the internet I went and I found data from the Beer Institute. The site has a downloadable excel with a wealth of beer information. The bar chart below shows active brewer permits by state and year. Use the slide filter to see the growth in permits issued.

Looking at the growth a little closer you can see YoY for the US as a whole and for each state. My home state of Maryland needs to step it up, while 40% is great, I know we can do better. :) Some states on the other hand are growing fast since 2004. Keep it up!

But the growth isn’t necessarily a great thing if you’re trying to break in the market. I’ve talked to the brewer of a local restaurant in Bethesda, MD and he says we’re going to start seeing many brewers close up shop. Many individuals are entering this space and not all will survive. But in the meantime, I’ll sample their beer.

Clearly California has the most active brewer permits, but it is the most populous state. How does 2012 brewer permits look when population is factored in? I found some data on adult population and blended it with the brewer data.  Note that adult here means 18 years of age or older, not 21, the legal drinking age in the US. I won’t go into it here much but I will say that I believe the drinking age should be 18, but the driving age should be 21.

Vermont has the least adults per active brewer permit and if you live in Mississippi, I’m sorry. I hope your state is importing other craft brews to help you out.

So now that I got you thinking about beer, you’re probably about to stop reading and head for the fridge. Wait!!! In order to help you make a selection, I scraped the web for prize winning beers. Using import.io, a fantastic web scraping tool, I downloaded a table of the Great American Beer Festival winners from 2013.

Use the filter to select the Category to view the Gold, Silver, and Bronze winners. I’m going to have to use this chart next time I’m on a beer run.

Now if you’ll excuse me, I just opened one of my favorites, Loose Cannon from Heavy Seas out of Baltimore. Have a great day!

loose cannon

 

 

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