Overlapping Column Charts: A Quick Actual v. Goal Comparison

Hello there! I’m writing you as a follow up to a workshop I recently facilitated with Nicole Huggett, MSW, for the Arizona Evaluation Network in Phoenix. A big focus of our time together was spent on covering visualization options for comparing goals and pre-post results.

One of the popular charts we discussed were overlapping column charts and how they can be used to compare actual performance to goals. Since the workshop, I have found overlapping column charts to be very valuable data visualizations for this – so much so that I knew I had to share the steps publicly (OKAY, I also kept getting asked for the steps, so I knew writing it once would save us all some time!).

Although I already shared when you might use this chart, the particular scenario I was to set is related to survey participation. Specifically, one community organization needed a quick way to determine which years they met (or didn’t meet) their survey participation goals. An overlapping column chart served as a great way to for project managers to determine just that in a matter of seconds.

Ready to make one yourself? Awesome – let’s do it!

To get started, select your data insert a 2D Clustered Column Chart.

Excel, we love you so, but you do some weird stuff. To fix the data, right click and choose select data. Go ahead and delete the year series (oh yes, we’re going to delete lots of things!), select Goal and notice the x-axis is empty…go ahead and click this button and highlight the four years. Voila! Your Goal Series is now included, and you should have two columns in your Excel window.

Next, let’s get these columns on top of one another. To do that, we are going to right-click the Actual Column, select Format Data Series (get familiar with this area of Excel – it’s crucial to a lot of your changes!), and change the axis from Primary to Secondary. The column you want on top is the secondary…and the column you want on the bottom is the primary.

Now that we have these on top of one another, let’s adjust the gap of the Goal column. You can play with the settings to make it look right but I’d say at least down to 75%.

To start to clean this up (it’s still confusing right now!), let’s right-click the Actual column (Excel should allow you to select all of them) and Add Data Labels.

From here on, it’s really turning your chart from a Basic to Bomb Chart (check out this example of how to make yours look awesome). You want to pay special attention to fonts (both the type and size), colors, unnecessary noise (yes grid lines, I’m talking about YOU), and, of course the title! It’s here where you want to leverage data visualization best practices to really get your reader’s attention.

After you’ve made some simple changes, your overlapping column chart it should look something like this:

One thing you might notice is we don’t know what the goal was from looking at the chart – and that’s OKAY. This is really intended to give high-level insight. In other words, was the goal achieved or not? Whether this is as much information as your exec team needs, or you want to create a dialogue, I highly suggest this minimalistic chart for easy actual-to-goal comparisons!

Want to know how to do this in Tableau? Tune in next time and don’t forget to check out my posts on how to Getting Started with Tableau.  

Formatting Tableau Filters!

Filters are awesome. One reason I love Tableau is that data visualizations aren’t static. How does that tie into filters? Glad you asked! Tableau makes it really easy for your stakeholders (i.e. end-users) to interact with their dashboard using filters. The only problem is that the filter you add on the back-end takes up a lot of room and, to be perfectly honestly, looks terrible. Luckily, I learned how to fix that, and I’m going to share it with you!

This post is most applicable when you’re visualizing data on a dashboard. So, assuming you have one, use a dashboard to test this out! I’m serious about keeping my client’s data private, so I am using a sample dataset.

The first thing you have to do is add a filter (well, make sure your monitored is plugged in too). Okay, okay, I know – not funny. But seriously, add a filter and select the dropdown “carrot” by hovering over the right filter. Opt to “Show Filter,” and be amazed as the filter options pop-up on the right side of your screen.

Deven Wisner Tableau Order Data Filter.png

Deven Wisner Tableau Show Filter.png

Jump to your dashboard, which would normally have a lot of other cool stuff on it. But, for the purpose of this post, we’re only focusing on the filter! So, you see the filter showing on the right side of your screen, and your years with associated number of records.

Deven Wisner Tableau Dashboard Jump.png

Now, as you can see, all your selections are there. You really couldn’t long for much more. Butttttt…it takes up a lot of space and it’s ugly. So, let’s hover over the right side of the filter box, click on it, and select the dropdown “carrot.” The first thing I do is change the filter to “Floating,” which means I don’t have to conform to Tableau’s organization of MY stuff.

Deven Wisner Tableau Floating Filter.png

You’re floating and awesome now, so you can go back to your “carrot” and select the formatting of the list. Personally, I use a dropdown but this will really depend on what’s appropriate for your data. What makes the most sense? For years, usually my clients are comparing, so I choose “Multiple Values Dropdown.”

Deven Wisner Tableau Multiple Filter.png

Now we are in business. But wait. That thing looks ugly, right? So, let’s remove the title by going to the “carrot” and choosing “Edit Title.” Delete it! Now, add a floating text box with something that makes sense. Remember: more text doesn’t always make something better. I simply changed this one to “Order Date: …”

Deven Wisner Tableau Filter Title.png

Deven Wisner Tableau Textbox.png

Of course, you want to avoid Tableau defaults. Customize your dashboards to your client’s needs AND their company colors. Canned visualizations are rarely the right fit for anyone, and I find that they’re too cluttered and clunky. More on that soon…!Deven Wisner Tableau Order Data Success.png

Want to learn Tableau? Start doing #MakeoverMonday.

About two years ago I was introduced to Tableau. I fell in love. The intuitive visualizations, live data connections, and easy-to-use interface. OKAY, maybe the last part is a bit of a stretch. Easy and Tableau in the same sentence? Sure, maybe if you’re using their Superstore dataset. Otherwise, look forward to some fun, yet challenging hours with Tableau. My plan is to tell you about #MakeoverMonday, and give you the opportunity to learn from my experiences (and hopefully inspire you to share your own).

Screen Shot 2017-07-25 at 8.23.31 PMAll that shouldn’t be a deterrent from using Tableau. There are more resources than most people would have time to exploit. I’ve tried several and found worthwhile tidbits in each. The most useful so far? Well, I have to be honest: #MakeoverMonday. Why you ask? Because #MakeoverMonday challenges you to create visualizations from data that wasn’t canned specifically for Tableau. You will find yourself searching for guides and input from other users. Instead of compiling endless notes on Tableau (that you may never use), you’re learning as you go…oh yes, my friend — applied learning!#MakeoverMonday HomepageSo what is #MakeoverMonday? The creators, Eva Murray and Andy Kriebel, call it a “weekly social data project.” Every Monday, a visualization and dataset will be available on their website. You can download the data for Excel or Tableau, so you can choose your favorite…or use both! After downloading, you have the chance to turn a preexisting dataviz into your own. Use your current skills, and stretch them with new tips and tricks. It can be as easy or difficult as you choose. Regardless, you’ll walk away with some swollen Tableau (or Excel) muscles. #MakeoverMonday Data SetsNow what? Learning is great. Sharing back what you learned, asking your colleagues questions, and engaging in reflective practice…now that’s the good stuff. Going forward, at least one of my monthly blogs will be dedicated sharing my dataviz makeovers. Even better, I will share the cool things I learn. Now go…build your dataviz muscles!

 

Got an applied project? You can build the capacity for data-driven decision making.

During graduate school, students are usually offered applied opportunities. What I love about applied psychology (e.g., evaluation, I-O psychology…) is that graduate students have the chance to bring their knowledge to a variety of industries — and built the value of data driven decision making. To me, that is priceless. Exposing the field of applied psych is great…and so is making others aware of all the great things can be done when something other than anecdotes are the decision making tool of choice.

So what’s my experience with this? I also had these great applied opportunities, and I started to realize that I was an advocate for my field. I had a new perspective about the entire experience — if my client walked away feeling like they wasted their time, I didn’t do a very good job.

My second to last semester I completed a needs assessment and process evaluation for a company in another state. This company is phenomenal — great idea that’s meeting a need, lean bottom line, and an office full of great people. Where’s the but? Well, it’s that data wasn’t driving their strategic planning. Needs assessment? What’s that? There I had it — an opportunity to BUILD the capacity of evaluation in this organization.

In short, the project went well — everyone learned a lot, services were revised, future planning was focused on data. But that’s all while you’re still there, right? In the back of your obsessive applied psych mind…you know this was one project, and the long lived method of luck and “educated” guessing (oxymoron’s make for good blog topics) could be revived and become the preferred decision making tool — again.

Data driven decision making. What does the data tell us? How do the statistics relate to what we are seeing financially? Your clients said they wanted this service…but was it a representative sample?? I felt like a broken record…because I hoped that between demonstration, dialogue, and bringing my client along for the experience would lead to an appreciation and PREFERENCE for data to inform their decisions.

images.png
Created by Chris Lysy

Well, two months later I was in a follow up meeting…sitting in on an unrelated project…and I heard it:

“We need to make decisions based on what the data tells us. It can’t be what I like, or what makes sense to me. Let’s use the tools we have to make changes using data.”

…you know that moment when someone says their idea of an awesome day is binge watching Frasier and eating pizza rolls, and you’re like “…that’s hot.” Bam. There it was. The sexy side of being an advocate for our field.

You see, change is hard. Pushing for a better method (that isn’t always easier) can be a challenge. And hey, being a grad student is a special level of hell at times. Sometimes you want to drop the results and peace. You don’t always want to screw with Excel for hours to get something other than a canned report (but seriously, talk to me if it’s taking you hours to craft good viz). But at the end of it, you have an opportunity to see the results put into action. Your very presence is a disruption — a potential catalyst for change. The credibility of our field? It’s on ALL of our shoulders. So, the next time you’re burnt out, remember the potential to impact decision makers and the responsibility to your colleagues (oh, and call on them when you need help!).

f29bf90a502b1268b067bfa6371168de.jpg
Created by Chris Lysy

I stopped blaming Excel for my basic data visualizations. Here’s when and why.

Early last year I realized my research findings only meant something if I involved the right people. And, to do so required me to be a creative and adaptive evaluator. Around the same time, I realized that I could have all the answers, but without a good way to communicate them, no one would actually notice.

Screen Shot 2017-04-02 at 8.32.46 PM.png
Look how easy it is to be basic!
I started searching for solutions. I found blogs by industry celebs (Ann K. Emery, and Stephanie Evergreen, to name a few) and began to pour over their archives. I found a lot of cool stuff. My world was quickly shaken when I realized that meant APA formatting might not be the best way for my voice to be heard (my condolences to the die hards, there is a time and a place). But, the big takeaway was that I figured out that, “Yes, it sucks because Excel made me do it,” was a crappy excuse for not visualizing my data in a better way. I learned to stop accepting the norm. You see, the purpose of Excel is to be a tool – not to do all my work for me because guess what? I’m the expert! I know those gridlines are garbage (and unnecessary), and that I should really figure out the RGB numbers for my brand.

So when did I stop blaming Excel? When I started valuing my data visualizations as more than entering data, selecting insert, choosing between a column or bar chart, and dragging it into my equally as boring report. When I realized that sweet visualizations in Excel just meant more thought and a few extra steps.

Screen Shot 2017-04-02 at 7.13.36 PM
I stopped accepting default settings!
Need an example? Check out Basic to Bomb Column Charts! Now go…find your inspiration for better visuals!

Screen Shot 2017-04-02 at 8.53.49 PM
Not too bad for a few extra clicks…