Invariably Ignored

Harold Ramis died this week. Most of you are saying to yourself, "Aw, that's sad... Now remind me who he is?"

Without Harold, modern society wouldn't be possible.

He wrote, directed, produced or starred in the most important films of all time. Caddyshack. Vacation. Year One. Animal House. Ghostbusters. Heavy Metal. Stripes.

Not to mention the most hated, most reviled, worst movie ever, Groundhog Day. I'm not kidding. Don't watch it. If you have watched it, forget it. Delete it from Wikipedia's Harold Ramis page. If you see it in the store, yell at the store manager and throw every copy in the trash. Then find Bill Murray and Zombieland his ass. Then do it all again. And again. And again.

Ignore it. Just like you need to ignore fields with your set analysis.

No Dumb Buttons

Oh, this your button, huh? A lovely button. Hey baby, you must've been something before electricity.

Listen... do you smell something? Maybe that since my last post, Kill the Button, that I'm on a button jihad? Calm down... how about a Fresca? I'm just trying to get you to stop making crappy* stuff. But this isn't about that, because you already know how to make a button a 'link'. This is a technique I learned from a post by Steve Dark. When you're using your button (link) to show/hide the Current Selections, tell the user if there's nothing selected.

Step One - Read Kill the Button

Make your button (link) that shows and hides the Current Selection box.

Kill the Button

A Priest, a Rabbi and Mormon Missionaries walk into a bar. The bartender says, 'Is this some kind of a joke?'

Half of you are giggling like school girls, half of you are waiting for the punchline, and the rest haven't read this post yet. Because if you have and you're smarter than the average bear, you'd know how funny that is! A simple joke for a simple folk...

Which brings me to one of my ongoing themes... Simplicity. Standard buttons in QlikView are not simple. They look like a blue NyQuil:

If you can't learn it after six times...

My mother (not shown above) once said, "Michael, if you can't learn it after six times, you're never going to learn it." She only used "Michael" when I was in trouble, so I actually learned this advice after hearing it only four times. Then my dad said, "Pick up the dog poop before you mow the lawn." Implied was the fact that it was time to mow the lawn. It only took me two times to learn that.

I haven't actually taken the time to count, but I think this is the sixth time I've taught you this:

QlikView is basically a fancy aggregation tool. 

In plain English, that means that QlikView takes some data, aggregates it (e.g. sum or count), then splits it up over a dimension (e.g. Quarter). If we aggregate (sum) sales over time (Quarter), we might get a chart that looks like:

Find and dandy. But what if you have something like advertising spend or MDF that affects sales? Generally, marketing activities affect sales that are not in the same time period, which means that you might want to compare MDF from FY12Q3 to sales from FY13Q1.

It's difficult to visualize the impact because your eyes have to focus on different groups of bars. It would be much better to look at FY13Q1 without also having to look at FY12Q2.

Since you guys are smart, I know what you're thinking... just use some set analysis to 'adjust' the time frame for the MDF. So you use an expression like:

Sum({<TimeFrame = {'Current Quarter - 2'}>} MDF)

Pleased with yourself, you hit OK. Cuss words flow as you notice that the MDF bars are gone from your chart:

The reason is that set anlaysis is evaluated once per chart, not per row. (In this case, the Quarters serve as the rows.) You've confused QlikView by saying 2 quarters ago. QlikView thinks, '2 quarters ago from what? There are 9 starting points! I'm not doing it.'

The solution comes from our old friends, ValueList and variables. And a visualization redesign.

Step One - Redesign

A grouped bar chart is not the best choice for this analysis. Sales will be orders of magnitude larger than the MDF spend, which means that any variation will be hidden in the relative sizes of the bars. A stacked bar suffers from the same problem. Since I occasionally give credit where it's due, the solution to this problem comes from Analyst Extraordinaire, David Stuart.

The interesting metric for this chart is the proportion of MDF to Sales. Instead of seeing a bunch of grouped bars, we'll present a line chart. Line charts are best used to see data trending over time, which is perfect for this analysis.

Much easier to see the ups and downs. One weakness is that the higher points in the chart mean worse performance. It took more MDF to achieve sales. This is a reminder that you may need to work with your clients to help them understand their data/results.

Step Two - ValueList

There are a couple of ways to create your ValueList. The first is to simply list the quarters you'd like to show:

ValueList('FY12Q1', 'FY12Q2', 'FY12Q3', ...)

But that's static and boring, and we do neither. A better way is to nest the variables.

Step Two A - Better ValueList
Decide how many quarters your clients would like to see in the chart. This example will show eight quarters.

• Create eight variables - vQtr1 through vQtr8 with Definition expressions that use relative dates:

Only({1<RelativeFiscalQuarter = {'Current Quarter - 1'}>} FiscalQuarterName)

• Create a ValueList variable (vAxis) nesting the quarter variables:

ValueList('$(=$(vQtr8))', '$(=$(vQtr7))', '$(=$(vQtr6))', '$(=$(vQtr5))', '$(=$(vQtr4))', '$(=$(vQtr3))', '$(=$(vQtr2))', '$(=$(vQtr1))')

Step Three - Offset Variable
Create a variable named vOffset. It will be used to let the end user decide the offset time between the MDF spend and the sales results. So, if the end user selects '2', the MDF spend will trail the sales by two quarters.

Create an input box for the vOffset variable.

• Select Predefined Values Only; uncheck Enable Edit Expression Dialog
• Check Number Series from 0 to 6, with  step of 1
• Select Predefined Values in Drop-down

Step Four - Calculated Dimension

Create a Calculated Dimension using $-sign expansion and your ValueList variable:

=$(vAxis)

Step Five - The Expression

This gets a little messy, but it's the magic that makes the technique work. Instead of evaluating the set analysis once for the chart, it treats each row (or cell, if you have multiple columns) as an individual chart. We've been through creating ValueList expressions before (here, here, here), so I won't go through the entire process, but here are the highlights for this solution.

• Your 'If' statement has a variable looking  at a variable. Make sure to put them in the order that you want the chart sorted by:

If($(vAxis) = $(vQtr8),

• The calculation for the point on the line is MDF / Sales. 

Sum({<RelativeFiscalQuarter = {'Current Quarter - $(=$(=(8 + vOffset)))'}>} MDF)

/

Sum({<RelativeFiscalQuarter = {'Current Quarter - 8'}>} Sales)

• The messiness on the numerator is a double $-sign expansion. It replaces the '8' in 'Current Quarter - 8' with the sum of 8 and the offset time set in vOffset.  
• Repeat for the other seven quarters.

The Result

The result is an interactive chart that your users can manipulate. They will be able to see how long it takes their MDF efforts to affect sales. Who am I kidding? The result is that you built something bitchin'!!!

Help me Rhonda

There were a lot of good things about the '80s. Apple Macintosh. Yugo. Fall of the Berlin Wall. Cyndi Lauper. Die Hard. My Gold Medal at the 1980 Summer Olympics. Ronald Reagan. Brits kickin' it in the Falklands Islands. DeLorean DMC-12. MTV. Pac-Man.

Unfortunately, the Beach Boys concert at Park West in 1982 was not one of them.

Help me Rhonda! I spent 12 bucks to see a virtual Bear Country Jamboree? There's no way those dudes were still alive! Animatronics DO NOT ROCK! Van Halen ROCKS! But don't get me started on Van Hagar...

Which brings me to the simple fact that I learn new QlikView things because people come to me for help. The latest was a request to only show the top 50 partners (in sales) no matter what filters were selected. On its face it's a reasonable and simple request. As a sales manager, I really don't care about the ankle-biters. I only want to see the Partners that make a difference.

Then I started to ponder the details.

Fightin' the Good Fight... On Pies.

Pies make great weapons. Delicious, nutritious weapons. They make stupid charts. Evil, lame, boring charts. And we all know that pie charts kill kittens.

So, in the words of Nancy Reagan, "Just Say No". Unless you're in Colorado or Washington. Then just say, "Dave's not here, man". Take some time, put a little thought into it, and you can come up with a much better illustration of your data.

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.