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

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 (2nd Dimension) and ‘Year’ is our time series shown on the X-Axis (1st 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

Advertisements
Comments
15 Responses to “The Indexed Time Series Chart – The Forgotten Technique (At Least by Most Qlikview Developers)”
  1. Brian Dunphy says:

    This is great. was working on this today. when will you get a chance to post the qvw?

    • qvdesign says:

      Brian,

      I should have the .qvw uploaded later today or at some point early tomorrow at the latest.

      All the best,

      Matt

    • qvdesign says:

      Brian,

      Link to example .qvw now posted.

      All the best,

      Matt

      • Brian Dunphy says:

        thats great.
        thanks. any clever way to exclude outliers from the output ? e.g. if 1 dodgy sale went through with a crazy price which effected the avg. ? so ideally you would be comparing price on each line with say the monthly avg and if its > or < 5% – exclude from output.

  2. Tom says:

    I don’t know if anyone else has noticed but I do enjoy the smiley face on each page! 🙂

  3. Nate says:

    quick question. How would you sort the legend from largest “gainer” to least? Thanks so much for putting this together. It’s awesome and very easy to implement.

    • Nate says:

      Also..as a follow up, when I created my version of the Index chart, it works perfect except it only shows values if I pick a “Country”. By default it shows “no data to display”. If I select all countries, it shows them all, but for some reason its blank when nothing is selected. Any ideas on that?

      • qvdesign says:

        Nate,

        As for only showing when you make a Country selection it’s difficult to say without knowing the dataset in question. I had a similar problem when I had Turkey included in my chart that had undergone a currency re-valuation, I therefore exluuded with a flag in the script but this caused a similar problem.

        If possible I’d try it with an alternative dataset and see if the issue replicates itself.

        Good luck,

        Matt

    • qvdesign says:

      Nate,

      To ascertain the ‘largest / Smallest Gainer / Looser’ you’d have to create some sort of expression in the Sort order, it’s probably possible but wouldn’t be entirely straight forward unfortunately.

      I’ll have a think and let you know if I come up with anything.

      All the best,

      Matt

  4. Shah says:

    Hi Matt,

    This is excellent. I’ve tried to use this to replicate a bar chart index, the same one as the economist one that inspired you. I’m wondering whether you can help.

    I need a bar chart that shows the index axes at the bottom or top, with bars starting from 100 (which is the middle point) and shooting off either side of the 100 index point where there is over representation of an x category, basically the same as that economist chart.

    Your help and advise will greatly be appreciated.

    Many thanks

  5. Lewis says:

    Matt

    I guess this would also work with Alternate states to give an indexed comparison against selections in both states?

    Lewis

Trackbacks
Check out what others are saying...
  1. […] For an introduction to the wonderful world of indexing (Now there’s a topic to introduce into diner party conversation; ‘Never mind you’re career as a brain surgeon, let me tell you about the wonderful world of indexing…why are you yawning?’) take a look at my previous index related post: https://qvdesign.wordpress.com/2012/05/09/the-indexed-time-series-chart-the-forgotten-technique-at-le… […]



Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: