Missed it by that much...

You've built a beautiful chart. Removed the Caption Bar, because we all know those are ugly. You changed the Title Settings to Divider, Left Justified. It's so beautiful that you want to copy it and use it with another metric. Just Ctrl-click and drag, but you missed the border by that much...

Try 99 times Agent 86, but you're too shaky. You should have had the small Red Bull, not the big one. That crosshair just won't stay on the border!

What to do? So simple you don't even have to click through to suffer through more bad jokes...

Just hold down Ctrl+Alt while you click and drag. You can click anywhere on the object. You can have the big Red Bull and not worry about the shakes. I wish I would have known about this a couple of years ago.

Sharing is Caring

Tweet This!

It hurts me to title this post 'Sharing is Caring'. Sure, I could try to work in a Care Bears joke. There's probably even a Valley Girl hook. Whatever.

But with good old Vlad puttin' the 'what's yours is mine and what's mine is mine' back in communism, sharing this is definitely worth a thousand rubles:

So, in the spirit of sharing everything you have with mother Russia, let's share your apps with folks that aren't privileged members of the party.

Session Collaboration

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.