Qlikview Timescaping – A Virtual Method of Data Time Travel (*No Delorean required)
To set the scene here I’m going to get all theoretical for a moment and make a bold statement; “Time travel (of a fashion) is possible.”…and we do it everyday, you’re probably reading this on a device that can allow you to both travel back in time to places & times you’ve never been as well as drop information and data of your own for people in the future that may be around long after you’re gone. I am of course talking about ‘data’ and devices such as a smart phone, tablet or PC that can create & read that data, be it transactions in a database, entries in a server log file, pictures, tweets, web site analytics etc etc – basically anything with a timestamp associated with it. This type of information is created on an ever increasing scale; some man-made; a photo or a tweet for example, some a combination of man and machine-made; a transaction entry in database perhaps and some that are purely machine made; regular entries in a log file being just one, it seems almost every piece of data casually has a timestamp added to it. This of course proves very useful; simply put we can sum and count between dates and this forms the core of many a Qlikview dashboard and quite rightly so but can we go further and actually use those date fields to more effectively and intuitively display information? Of course we can; it wouldn’t be much of a post if the answer was ‘no’ would it.
I’m all for adding date dimensions into Qlikview dashboards; it’s one of the best things about Qlikview: select a group of sales people, select Q1 2012 see a sales figure, one click and instantly you can see the Q1 2011 figure, it’s one of the great selling points of Qlikview so what I’m proposing here is in no way meant to replace that functionality; it’s purely an addition. The basic principal of what I’ll demonstrate is giving the dashboard user the ability to make selections in the usual Qlikview way to isolate a dataset be it a particular days sales, a certain servers activity, web traffic from a certain area or as in my demo a particular training session and then enable them to press ‘play’ and see the data as it happened either in real-time and speeded up of slowed down as required.
Some will no doubt be thinking ‘isn’t that similar to the animated dimension on a chart?’ and yes it is but this is much more flexible (we can use it everywhere) and crucially it can be used to show the current position relative to the whole; which is much more useful.
Download the .qvw here: https://docs.google.com/open?id=0BxloTMUod74tT2REeHNld3VwcGs, open it up and press the ‘Play’ button in the top left hand corner as it will quickly demonstrate what’s being shown.
To demonstrate the core principal at play here I’ve created a dataset for an imaginary training session that could easily be created genuinely using readily available off the shelf hardware; we’ve got a Timestamp (of course), GPS co-ordinates, an Altitude, a Body Temperature, a Heart Rate and Calories Burned. I’m going to use Qlikview to analyze the session and replay it to show insights like ‘At the 3rd corner 1min 32sec in your heart rate was 152, it then remained flat from there on.’. This could just as easily be a sales volumes or server activity (the data would need to be intervalmatched() or floored() or rounded() appropriately in the data but that’s reasonably straight forward.
The Timescaping effect is created by using a series of variables in conjunction with the now() function and the Timestamps from the data:
- A variable stores the earliest timestamp in the dataset, this responds to selections and in my demo’s case is simply =min(Stamp).
- I’ve created a play button with an action that sets a second variable to be =now(1) as soon as the button is pressed. We now have two timestamps, one from the data that could be from any point in the past and one recently created in the front end.
- A 3rd variable is set that holds the difference between now() and our second variable that was created when we pressed play. If we pressed play 20sec ago then the 3rd variable will hold 20 then 21, 22, 23…
- To Timescape we simply add our difference between variables 2 and 3 to our minimum timestamp from the data to give us the timestamp from the data we should be looking at had we pressed play at the time of the minimum timestamp, this time is held in a 4th variable.
In my example I’ve used a multiple to allow us to speed up time so for every second in our time we can move through the data in multiples of 4, 8, 32, 1,000 or whatever is required to suit the actual data, I assume we could also slow time down should we need to and if the data supported it.
From then on it’s just a case of setting the Charts, Tables and Text Objects with the appropriate expressions to show say what the Altitude value was at the timestamp nearest that held in the 4th variable which is done via expressions similar to:
This then allows us across one dashboard and several charts to show how key metrics have changed as time has progressed in a dynamic, natural way BUT to also show then in context of the overall which is where the real benefit comes from.
I’ve also added the functionality whereby the replay can be paused at any point to allow further analysis of the information. So for instance I can use the map to pause when I reach a certain point on the course, the timer a certain time into the session or any of the other metrics for that matter.
I’m not suggesting that this be used to say replay all a business’ orders as they came in or a server’s activity but for it to be more of a specialist tool for instances where say a server has run into problems you could replay the data for the preceding minutes to get a view of all the relevant metrics as you approached the problem point.
Of course the majority of this data could be shown in traditional timeline charts and they’re in most cases the best solution but for me animating the data in this way adds that extra level of engagement that can make the certain data sets much easier to interpret and absorb.
Please forgive the crude layout of the dashboard; it’s a very early proff of concept prototype, I’ll be working at tightening things up and adding information in the coming weeks.
The .qvw can be downloaded here: https://docs.google.com/open?id=0BxloTMUod74tT2REeHNld3VwcGs
As always I hope you find this technique of use.
All the best,