Qlik Tips: Part-to-Whole Time Series

I recently talked (here) about using pie charts for Part-to-Whole comparisons. As Stephen Few explains in his excellent article, Displays for combining time-series and part-to-whole, you can't really use pie charts like this in a time-series. It just doesn't work.

Stephen suggests the use of line charts (which are always a good idea for time-series) with one line showing the total and then the "normal" line chart showing how the total is broken down.

So, how do I create this type of chart in QlikView?

The easiest way is just to create 2 charts, one for the total line and one for the split values. This works fine - unless you want to minimize the objects or want to put them in a container control. Then you run into problems.

There are a couple of solutions for this that I have been playing with. The first one, which is more "dynamic" for the values in the dimension, does not always work out great. Here it is:

So, how did I do this?

I have 3 dimensions in this chart. The 2nd and 3rd dimensions are the Year and Category - just as they would be for the normal split out chart. The first is using the ValueList function in QlikView. ValueList creates a pseudo dimension containing, as its values, the list of values that you provide in the function.

My calculated dimension expression is:

=ValueList('Total', 'Categories')

So, I have a dimension that contains 2 values. I then turned on the Trellis function for this dimension which achieves 2 separate charts for Total and Categories.

I then use ValueList again in the expression to test which of the values I am at so that I can use a different expression for each of them:

if(ValueList('Total', 'Categories') = 'Total',
Sum(TOTAL<Year> LineSalesAmount),
Sum(LineSalesAmount))

So, if it is the Category then I just use the simple expression. For the total I use Total to get the same value for each category and hence achieving just one line in that chart (although there are actually 3!).

All very clever but there is one flaw with this. Because of the trellis, the axis are going to be the same height. This means that in the bottom chart, the lines for each category are a bit squashed down in the lower half.

So, another solution. This one is a little more "static" on the values because I need to define an expression for each of the values in the dimension. I am not sure that this is such a problem because, in the "real world", many of these dimensions are quite static anyway.

So, I have a simple chart with Year and Category for dimensions. I have an expression using a set for each of the 3 category values like this:

Sum({<CategoryType={"Footwear"}>} LineSalesAmount)

and one expression for the total like this:

Sum(TOTAL<Year> LineSalesAmount)

So I do something that has been in QlikView for years (so this will work in v8.5 where there is no Trellis). In the "Axes" tab of the chart properties I set the option for the Total expression to appear on the Right Position and then turn on the "Split Axis" option - like this: