The Indexed Time Series Chart – The Forgotten Technique (At Least by Most Qlikview Developers)

Posted by qvdesign on May 9, 2012 · 15 Comments 

I really should have posted about Indexed Charts earlier as they’re something I’ve long wanted to promote in the Qlikview world and always wondered why I’ve never before seen them deployed; with the right data they’re nigh on essential to deliver the insight users need.

Why are Indexed Line Charts so useful, surely a normal line chart would do?

If that’s what you’re thinking then how wrong you are. Standard Line Charts are great, this isn’t a post that for one second should discourage you from using them it’s just in some circumstances and Indexed Line Chart is more suitable AND can take your dashboard from merely reporting what’s happened; ‘We had sales of X in 2010’ (which is perfectly OK if that’s all you need) to offering real, valuable insights that can’t otherwise be gleaned from the data; ‘Sales of Product A are increasing at a faster rate than Product B’.

To illustrate the point of Indexed Charts being in some circumstances more appropriate than a standard Line Chart take a look at the example on the left below taken from The Economist (who incidentally use Qlikview – although not to create this chart):

The chart is basically showing projected population decline in several EU countries over time, this could of course be shown on a standard Line Chart but it wouldn’t necessarily show the info we need as well as it could be. The issue is that the starting populations of sayGermany(82.8m) and The Netherlands (15.8m) would be wildly different if shown in a standard line chart. ‘What’s the problem with that?’ Well, in many cases there is no problem as long as you’re only wanting to report static ‘this happened’ style information but if you want to show relative change (ie: Who’s population is declining fastest) it would be misleading. If using a standard Line Chart imagine that both Germany with a large population and The Netherlands with a relatively small population loose 10% of their respective populations between 2005 and 2010, the fall of the German line would appear to be greater than that of The Netherlands even though both lost 10%; it would appear as though Germany has a greater problem than The Netherlands which wouldn’t necessarily be the case. This is just one simple example; imagine if you wanted to show changes in currencies’ exchange rates against the US Dollar over time; we may have UK Sterling would be around £0.6 = $1 whilst also trying to show South Korean Won which would be nearer 17,000SKw = $1 and those 2 lines could never be shown on the same chart (fluctuations in the Sterling line would be nigh on invisible – unless a log scale was used, but they’re awful in my view) that is of course unless we use an Indexed version. The same could apply to Sales; some products may sell millions each period but others may sell only a few hundred so how do you effectively show them all on a chart to asses the impact of say a marketing campaign – with an Index of course; it’s the great leveler; no matter your value you’re all treated the same.

So what is an Indexed Line Chart? Firstly the easy bit; it’s a Line Chart (and not a very complicated one at that), OK, so what’s an Index? In this instance an index is our start position of 100 (It’s not a Line Chart of an Index!?). No matter whether you’re population at the start of the series is 1.5m or 341m the chart shows 100, if you’re ex-rate to the Dollar is 0.23 or 34,958.32; it doesn’t matter it shows as 100 and never anything else. From then on the line shows how subsequent years (or whatever time period you’re using) values are relative to this 100; if we’re up 10% the value will be 110, down 5% relative to our start point it will show 95, it’s that simple.

The key to an indexed chart is something I’ll term; ‘The Index Factor’, this is the value that has to be applied to each period’s value to make it relative to 100, this is calculated via the following simple method: Sales, Population, Ex-Rate or whatever you wish to show as an Index for the first period on your X-axis divided by 100. It’s as easy as that, we now have our Index Factor. So in the case ofGermany’s population in 2000 we’d have 82.8m/100 giving us an index factor of 828,000. To get to our 100 needed in the chart we take our initial value of 82.8m and divide it by 828,000 and get surprise, surprise: 100. When we move to 2005 when the German population was 82.4m we take that and divide it by our index factor of 828,000 and get 99.52. The key is that the Index Factor remains the same across our time series whilst the population, ex-rate, sales etc fluctuates.

Of course all of this wouldn’t be much use if we couldn’t use it in Qlikview so below is one of my examples (there’s a link to the .qvw at the bottom), I’ve gone a little overboard on the Economist theme but they were the ones who introduced me to Indexed charts and it’s their dataset so I feel I owe them! (and I like the challenge):

This is taken from a small Qlikview App that analyses The Economist’s ‘Big Mac Index’ from 2000-2010 (http://www.economist.com/node/21542808). The Index looks at the annual local currency cost and the US Dollar cost of a Big Mac each year from a range of countries, thus being an unscientific but surprisingly accurate reflection of exchange rates to the dollar, country development and world commodity prices. Normally it’s published year on year but after hunting around the web I’ve managed to pull them all together to generate a reasonably complete time series.

When we’re looking at the cost of a Big Mac in the local currency we’d never be able to use anything other than an indexed chart as we’d need to cover everything from a Big Mac in Indonesia at 22,780 (Rupiah) and one in the UK at 1.90 (GBP), in unit terms (and how it would be shown on the chart) the Indonesian Big Mac is 12,000 times more than the UK equivalent, a definite case for indexing.

Sticking with the Big Mac theme; here’s the ‘Secret Sauce’ of the chart (and nearly all Indexed Line Charts) that I came up with to quickly and easily deploy Indexed Charts:

=sum([In local currency])/(sum(total <Country> if(Year=min(total Year),[In local currency]))/100)

Where ‘In Local Currency’ is what we’re showing on the line so could just as easily be ‘Sales’, ‘Costs’ or ‘Volume’, ‘Country’ is the dimension we’re splitting the lines by (2^nd Dimension) and ‘Year’ is our time series shown on the X-Axis (1^st Dimension). All you need to do is swap out the fields for your own and you’re away. The expression is dynamic so if you select a new date range the new minimum period will form the indexed 100 start point. You can also calculate your date periods using the normal date functions such as monthname() without much difficulty:

=sum(Sales)/(sum(total <Product> if(monthname(Date)=monthname(min(total Date)),Sales))/100)

In the above chart we have an X-Axis based on a Calculated Dimension of Monthname(Date).

Whilst the above example fits many situations indexing can be far more complex depending on the dataset, the below example is taken from an Global Exchange Rates app I created and would only work with some complex Set Analysis, I’ll write a post about it at a later date:

Even though this dataset contains the virtual equivalent of over 32m records (Each ex-rate for each day is calculated on the fly via triangulation against the known US Dollar ex-rate pulled from the web) the chart still responds instantly which shows how flexible (and light on CPU) indexed charts are – there’s no reason not to use them…when the time’s right.

A copy of the Big Mac Index .qvw can be downloaded here: https://docs.google.com/open?id=0BxloTMUod74tTE40dE0tU0ptbTQ It’s a liitle heavy on the ‘style over substance’ but it was also my aim to pay homage to the Economist visual style.

For more Economist chart goodness: http://www.economist.com/blogs/graphicdetail (I access it via RSS > Google Reader > Flipboard for iPhone & iPad)

As always; I hope you find it of use.

All the best,

Matt

Filed under Essentials, Visuals