Good vs Evil (or "How to confuse with a poor bar chart")

There's good and there's bad. And then there's BAD:

And since the Muscles from Brussels knows no bounds, he played both good and bad in his Oscar-worthy masterpiece, Double Impact.

And since we sometimes refuse to channel our inner van Damme, we create bad bar charts:

We've all done it. Sales by Year by Month. Lots of colors. Lots of data. Almost no insight. This is your Double Team where you make Dennis Rodman look like a real actor. We can fix it, just like they fixed Jean-Claude in Universal Soldier.

Theory

Normally, bar charts should be used to compare groups of data. For example, you might want to compare sales for OfficeMax vs Office Depot vs Staples. A good way to do that would be to use three bars so you can easily compare the heights (bigger bar = more sales). A line chart is usually the best choice for trends. Your x-axis might be year-month and you can easily see how sales have changed over time. It's pretty easy to see that sales have generally gone up over time:

The problem with this chart is that what you really have is a combination of time trends and groups. After this really easy, although very clever tip, we'll end up with a chart that gives us both:

Grouping

One of my dashboard rules is that your users should look at your creation and think to themselves, "Of course it should look like that." At the same time, you should be bragging to all of your peers, "I AM SO FREAKIN' AMAZING! LOOK WHAT I DID!" The chart above accomplishes that. It groups by year, groups by month, trends over time, and tells the user how this year compares to the other years.  That's freakin' amazing.

Unfortunately, QlikView doesn't make building this very intuitive.

Step One - Make the Chart

This is easy. Make it like the colorful, ugly one. Month and Year as the dimensions; Sum(SalesAmount) as the expression. Click 'Finish' and cringe at the result.

Step Two - The Trick

The trick to grouping the months into years has two parts. First, add and expression:

=1

Seriously, just put =1 as the expression. Next, uncheck 'Show Legend'. While you're at it change the 'Bar Distance' to 1 and the 'Cluster Distance' to 5.

You'll end up with something that looks like:

Better, but still boring.

Step Three - Colors

A good Thumb Rule is:

Recency = Importancy*

We can achieve it by highlighting the max year in the chart.

Open the Expression Attribute for your sales and use the following as the Background Color:

=If([Year] = $(=Max([Year])), ARGB(255,0, 109, 44),
  ARGB(50,0, 109, 44))

That expression will make the opacity of the max year of the chart much higher, increasing it's Importancy.

Step Four - Reference Lines

Notice in the chart that there are two reference lines. The top one is the maximum monthly value for the most recent year. The bottom is the minimum value for the most recent year. Those lines let the user see what the monthly range of sales for the current year is compared to other years. It's an unobtrusive way to see if there are any monthly outliers.

Add a Reference Line and use this expression:

=Max({<[Year] = {'$(=Max([Year]))'}>} Aggr(Sum([SalesAmount]),[Year],[Month]))

Change the color to match the green that you used for your bars. Change the line Style to dotted. If you like, check the Show Label (and put something meaningful in there).

Add another and change Max to Min. Don't forget to change the color (I used red).

Epilog - You're own personal Expendables 2

And now, you're as close as you'll ever come to feeling the awesomeness of Jean-Claude's glory in Expendables 2. Bask in it!

* TRADEMARK just like Pat Riley's Three-Peat!