How to Make YOY Bar Charts in Tableau | LunaMetrics

How to Make YOY Bar Charts in Tableau

/

blog-YOY-charts
To make year-over-year bar charts in Tableau, follow my step-by-step guide below. You’ll learn a few Tableau basics that help with other data visualizations, too.

The Goal: Chart side-by-side bars for each month

Here’s what I want: Revenue for each month for the year to date, vs. the same month last year. By including the months at the end of last year, I can see what’s coming if this year follows the same trend.

Tableau side-by-side bar chart showing YOY trends by month

Step 1: Connect to the data

I’ll start with an Excel file that has a flat table of data with 3 columns:

  • Month = Jan, Feb, Mar, etc.
  • Year = 2014 or 2015
  • Revenue = amount of revenue for the month and year in the first 2 columns

When I connect the Excel file to Tableau and go to my worksheet, I see Month and Year under Dimensions, while Revenue is under Measures. Tableau makes an educated guess where each item belongs, and this time it guessed right.

Tableau dimensions and measures in a new worksheet

What’s not so easy to see is that Month is blue, while Year and Revenue are green. This will turn out to be important!

Step 2: Drag and drop Month and Revenue (or other measure)

I know I want revenue by month, so I’ll drag and drop the Month pill onto the Columns shelf, and the Revenue pill onto the Rows shelf. This gives me a bar chart by month, but it’s not quite what I want.

Tableau bar chart showing revenue by month before separating YOY data

Notice that the bars for January through August represent the total revenue for both 2014 and 2015. I still need to separate the data by year.

Here’s where things can go wrong, if you don’t have a guide to warn you. Lucky you, here I am. “Danger, Will Robinson!

Step 3: Convert the Year dimension from continuous to discrete

Before you drag and drop Year anywhere, you need to change its data type. Tableau made Year a continuous data type because it’s a number. For our bar chart, though, we need to change it to discrete.

If you’re not familiar with the terms continuous and discrete, just stick with me and you’ll see why this is important.

Under Dimensions, click Year and select “Convert to Discrete” as shown below.

Tableau dimension converted to discrete

Notice that Year has changed from green to blue, indicating that its data type is now discrete. “Discrete” means that a dimension can take a countable number of values, in our case, exactly two: 2014 or 2015.

“Continuous” means the dimension has an infinite number of values, e.g. from 2014 to 2015 and all the decimal numbers in between. Tableau will not make bar charts with a continuous dimension; it will make line charts instead. (Try it and see!)

Step 4: Drag and drop Year

Drag and drop the Year dimension onto the Columns shelf. Both Month and Year should be blue pills, indicating they are both discrete data types. And your bar chart should look like the one below. Almost there!

Tableau bar chart showing revenue by month after separating YOY data

All that remains is to change the color for each year and do a little cleanup.

Step 5: Add color and clean up

Give each year its own color by dragging and dropping Year from the Dimensions list onto the Color field under “Marks”.

Tableau YOY bar chart before cleanup

I’ve edited the colors to match the way Google Analytics shows YOY comparisons, with orange for last year and blue for this year. To edit colors, click the top right corner of the Year legend.

To clean up the unnecessary labels at the top and bottom of the chart, do two things:

  1. At the top of the chart, right-click Month/Year and select “Hide Field Labels for Columns”
  2. In the Columns shelf, click Year and un-check “Show Header”

The result should look like the chart at the top of this post.

Bonus Step: Add % Change to the tooltip

For year-over-year data, you may want to add the percent change from the previous year. It’s easy with Tableau’s table calculation feature.

I recommend adding percent change to the tooltip, which you’ll see when you hover over each bar in the chart.

Tableau YOY bar chart after cleanup, plus tooltip

First, drag and drop Revenue from the Measures list to the Tooltip field under “Marks”. Then click on the Revenue pill and select “Add Table Calculation”.

Tableau YOY bar chart tooltip with percent change

For the table calculation, choose “Percent Difference From”. Calculate the difference along Year, and display the value as a percent difference from Previous. Click OK.

To change number formatting, click the pill for that measure and select “Format”. To edit the wording of the tooltip, click the Tooltip field under “Marks”. And that’s it!

Have you run into any issues creating year-over-year or other side-by-side bar charts in Tableau? I’d love to know how you’ve created Tableau charts like this or related visualizations. Please share in the comments.

Dorcas Alexander is the Analytics Department Supervisor. Her path to LunaMetrics followed stints in ad agency creative, math, and computer science. Dorcas has a master's degree in language and information technologies from Carnegie Mellon University, where she helped build precursors to a Universal Translator. One of the top-rated tournament Scrabble players in Pennsylvania, Dorcas has an insatiable drive to compete and win.

  • Maria

    Hi,

    I have a similar chart, but rather than the bars I am using a line graph. The axes are the exactly the same.

    Is it possible to connect the lines so that 2014 is one color connecting each month and 2015 is another color connect each month? Every time I try to do so I just get a line connecting 2014 to 2015 under each month.

    • Dorcas Alexander

      Hi Maria, To create a line graph for this type of data, you need to combine the separate “month” and “year” fields into one “date” field. I tried this out on my data using the instructions in the following article: http://community.tableau.com/thread/146656. I added a step to convert my “months” from string-type to integer-type, by creating a calculated field with the formula: IF([MONTH]=”Jan” THEN 1, ELSEIF([MONTH]=”Feb” THEN 2, etc. END. Of course if you can get your data from its original source with a proper date field, then none of these steps are necessary.

    • Dorcas Alexander

      Hi Maria, I thought you would have to combine the month and year into a date field to create a line chart, but I found another way to do it. If you have separate month and year fields, as I do in my data, and you have created the bar chart as outlined in my post above, there is an easier way. Start with the bar chart, then go to the “Marks” menu and select “Line”, and remove the “Year” pill from the Columns shelf. When I tried this with my bar chart, it converted to the YOY line chart like the one you want.

  • Eddy

    Hi,

    Is it possible to show zero values for, in this case, sept, oct, nov and dec. The empty months are now a bit unclear for me.

    • Dorcas Alexander

      Hi Eddy, Yes, you can add the zero values to your charts with the following step: Click on the blue “pill” in the column shelf and place a checkmark next to “Show Missing Values”. This option is available whenever your data for that dimension has missing values in it.

      • Eddy

        Wow, that was way more easy than I thought. Thanks for the quick reply!

      • Woochul Lee

        Hi Dorcas,
        Thanks for the great post – I am trying to do a similar thing (showing 0’s for the rest of the months), but I don’t think it’s working because I have no data for future, upcoming months. I have checked “Show missing values” on my blue pill (paid date, in my case), and still not showing 0’s. Is there anything else I can try? Thanks,
        Woochul Lee.

        • Woochul Lee

          Here is the screen shot of the view

        • Woochul Lee

          Here is the screen shot of the problem – thanks!

        • Woochul Lee

          Here is the screenshot of the problem – let me know what you think – thanks!

        • Dorcas Alexander

          Hi Woochul, My chart does not show data for future, upcoming months, either. While I do not have enough experience with Tableau to give you the solution, I did a quick search of the Tableau user forums and found the following answer which may help you: https://community.tableau.com/thread/152202

  • Oli

    Hi Dorcas,

    Instead of separate columns/fields that contain month and year, I have 1 field that is of type date. How do I create a similar YoY bar graph comparing the same month between different years?

    Thanks in advance.

    oli

    • Dorcas Alexander

      Hi Oli, Assuming you have the Date pill on the columns shelf, and a measure pill on the rows shelf… here’s what to do. Click the plus sign on the Date pill so that it expands into pills for Year, Quarter, and Month. Remove the Quarter pill from the columns shelf. Switch the Year and Month pills so that Month comes first on the shelf, then Year. Change the Marks from “Automatic” to “Bar”. Under Filters, click Year and drag it downward, dropping it on the Color icon under Marks. That should do it!

      • Oli

        Awesome, it worked! After I switched the month pill to go in front, it magically worked! I didn’t drag the Year downwards under Filter though…

        Thanks so much!

  • Chumki

    Thank you so much for the post…it helped me in developing a YOY graph easily.

  • cakeholes

    Hi Dorcas,

    I have data coming from a cube which I cannot modify and as such my only date Dimensions are Fiscal Period (stored as 01/2015, 02/2015, etc. which is actually Feb and March) and Fiscal Year which runs Feb through Jan. This has caused me a ton of issues however, I was able to do most of what you did here but stuck on a couple of things. I can’t seem to format or otherwise change my Fiscal Period Header to show month (only shows as 01/2015), also 01/2015 and 01/2016 are shown as separate lines (see attachment). Not sure if you can help on that one or not. The last issue I have is with the YoY seems to work after some fiddling that differs from your explanation (likely due to the date Dimensions)…it seems to calculate fine on 2016 over 2015 by fiscal period however, when I hover over a 2015 period, for example 03/2015 it shows the % difference from 02/2016. Any thoughts on how I can fix that?

    Any help is much appreciated!

    Kevin Coles

    • cakeholes

      Hi again Dorcas,

      So on a lark I whipped up a quick SQL table to simulate what you did and sure enough I can do it exactly as you have so clearly my cube data is a sticking point here. For the record I am using a bundled (for lack of a better name) version of Tableau. My client uses the Deltek Vision ERP system (that’s what I work with primarily) and they now bundle Tableau with their Visual Performance Management module. From what I can see they have probably somewhat locked this down (e.g. it’s not allowed to connect to Excel!) to suit the Cubes that they have developed around their database. What I have found is that I cannot make calculated fields using any of the Dimensions…only from Measures. My hope was that I could use CFs to get the date formats I need but it doesn’t seem possible. I could modify the cube however since it refreshes nightly from the database and is subject to change with each new release of the software that might become a nightmare to maintain.

      Would love to hear if you have any suggestions or workarounds for these bizarre limitations.

      Cheers,

      Kevin Coles

      • Dorcas Alexander

        Hi Kevin, Not being able to use calculated fields on your date dimensions – that does sound frustrating. Especially since you don’t have true “date” types, i.e. your date dimensions are coming in as character strings instead (marked “Abc” in the dimension list). This data type issue probably contributes to your YOY calculation problem, too. Maybe a formula that only calculates YOY change if the current year is greater than the previous year – I’m not sure what that would look like, but that’s the approach I would try.

        • cakeholes

          I’m going to experiment with a mapping table to map the values I know will work with the Cube formatted Fiscal Periods…I found a secondary benefit in that since the Fiscal Period of 01/2016 is actually February not January I can map this at the same time. It appears this tableau instance will allow me to add a secondary data source and edit the relationship of the two so I think this might work…I’ll post if it does!

          FYI it won’t let me create CF from any Dimensions, not just the dates. Only Measures appear to be available. I can’t really see why this would happen…is it the Cube Data or the Tableau Interface they have locked down…Deltek offers little to no support on it either.

          Thanks,

          Kevin Coles

          • cakeholes

            Latest update….a SQL mapping table works! I got it almost where you have it save for one thing…the YoY difference seems to be troublesome as I have measures/dimensions coming from SQL and from the cubes. Not a deal breaker but sure would be nice if I could get that to work too. The mapping table is a bit cumbersome but it wouldn’t take long to build it out for the history and for 5 future years or something in Excel and dump into the table.

            For some reason the YoY returns null no matter how I slice it.

Contact Us.

LunaMetrics

24 S. 18th Street, Suite 100,
Pittsburgh, PA 15203

Follow Us

1.877.220.LUNA

1.412.381.5500

getinfo@lunametrics.com

Questions?
We'll get back to you
in ONE business day.