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'!!!