New Qlikview Chart Type: Dynamic Network Flow Charts

Straight away I’ll say something: this is my favorite chart type amongst the several I’ve created in Qlikview over the last few years…and I only knocked it together on Monday.

I’ve wanted to create a chart like this for a while but never had the time or more importantly the inspirational leap required to get there, however; that changed with a little push from a recent post on another Qlikview Blog. Last week Stephen Redmond posted his Qlikview based version of Charles Minard’s famous visualization of Napoleons march to and retreat from Moscow, the original post can be read here: http://qliktips.blogspot.co.uk/2012/06/homage-to-minard.html

I’ve come across this chart many times (https://qvdesign.wordpress.com/2012/04/13/book-review-the-visual-display-of-quantitative-information-edward-tufte/) and it never seems to loose any of it’s resonance so it was great to see it rendered by Stephen in Qlikview to effectively show Qlikview’s flexibility but it lead me to my usual train of thought….’what more can be done with it’. My first thought was that it would be great to plot the chart over Googlemaps to give more resonance to the distances involved but whilst technically possible this proved to be problematic from the outset due to many of the places named in the chart not being there anymore or no longer using the same name. However all was not lost as that ‘overlay a line chart over a map’ idea was all I needed for inspiration to reach the chart shown at the top; if Stephen could use a line chart to create his version of Minard’s chart then surely it was possible to create a Network Flow chart.

Another chart also deserves a mention here as it does in Stephens post as it showed what is possible, it’s the Parallel Co-ordinates chart that came out of Qlikview Labs a few years ago, a full overview of the chart (as well as a .qvw) can be seen here: https://qvdesign.wordpress.com/2012/01/31/brilliant-qlikview-parallel-co-ordinates-chart/

So, what are we showing here and how are we doing it.

Firstly I’ll outline what’s being shown in my version of the chart which will hopefully show how useful this chart type can be. One of my current clients is a Global Insurance company, they have numerous offices around the world generating business which all feeds back to their Head Office in The City of London at the Lloyds of London Insurance Market. As a global business they need to effectively see how income, business and exposure are generated across their offices and what better way to show that than a dynamic network flow chart? Download the .qvw from the following link as it will quickly show the simplicity and potency of what this chart shows: https://docs.google.com/open?id=0BxloTMUod74tQ3dCSHhybnlmY2c

The chart is entirely reactive to selections in the data so users can drill into the data and see instantly how those selections play out on a global scale, cycling through the ‘Division’ listbox really shows this off. For instance we can quickly see that when we select ‘Oil & Gas’ that the majority of business comes via the Tokyo office, or when we select ‘Mining’ that the business is spread across 4 main locations with no small contributors.

For me this is information couldn’t be shown to the same degree via another method – you could of course plot a scatter chart over the map but for me they end up sometimes being a terrible mess and are only good for indications and not pin pointing sources. Also in true Qlikview style this chart type helps identify where isn’t contributing or is contributing to a lesser degree as well as simply those offices that do generate business. Put simply; I love it, I’ve spent the last week just cycling through the selections to see what the chart tells me – I know it’s not perfect and it’s not a ‘one chart fits all’ solution but none the less; I really love it.

Enough with the gushing hyperbole; how’s it created:

You should have guessed by now that we’re using a Line Chart just as the Parallel Co-Ordinates does and Stephen’s Minard chart – I did try something similar with a Scatter Chart in much the same vein as my Indexed Explosion Chart – https://qvdesign.wordpress.com/2012/01/23/new-qlikview-indexed-explosion-quadrant-chart/ but it doesn’t allow for the same level of control over the line styles.

Next we have a static image of the world simply sourced from the web via a GoogleImages search. It’s important to note at this point that the image is really what drives this chart and is the foundation for everything to come. We can use the world as we are here but we could just as easily have a Visio diagram of a network, a map of a local area or any number of other appropriate images – changing the image completely alters the chart.

Over the top of the image we need to create our Line Chart it has a transparent background to allow the image to show through and it must also have it’s X-Axis set to ‘Continuous’. It’s now time to plot the datapoints which is the most tedious element of the chart but it’s not too difficult so persevere. We’re creating a Line Chart with X and Y axes running from 0-200 (for some reason I went for -100 to 100 on my X-Axis) which will then allow us to create X & Y co-ordinates to plot where each office is located. This has to be done manually, personally I found the location on Googlemaps and then matched it up with my map image and through trial and error positioned the datapoints, so for instance the Labuan office is located at X=58 and Y=105.25 in my example – this is where having the X-axis as continuous is essential as it means we’re free to plot to any value in the range. I’m a geek so I enjoyed turning this into a game and trying to place the datapoints from memory and with as few trial & error guesses as possible…as I said; ‘I’m a geek’. Once we have a few datapoints we can get the chart to start working: the primary dimension should be our X value and the secondary should be our Office with ‘=Y’ being the expression, obviously take things like the Title in Chart and Legends off to neaten things up. What we should have at this stage is something that looks like a Scatter Chart as we only have one datapoint per location and you need 2 to create a line so we need to add some more datapoints to drive the creation of the lines. As all lines are flowing to the London HQ we need to associate each Office with the X & Y co-ordinates of the HQ, once loaded this will give us a straight line between the office and the HQ (or the Server and Network Load Balancer if that’s what we’re showing), we then create the curve by adding a 3rd associated datapoint to pull the line away from straight so our data looks like this:

As I said it’s a bit tedious but I plotted all the offices used here in about 30mins so it’s not that bad. As a point to note; as this is a Line Chart everything flows from Left to Right so you can’t create loops in your curves – the curve datapoint must be the middle one on the X-axis.

Once we’ve loaded some data and associated it with our Office names the final thing to sort is the thickness of the lines and I’ve used the same technique as the Parallel chart and Stephen’s, I’ve reduced the maximum thickness as a width of 8.5 is too much for me so my expression looks like this:

='<W’&num(6/100*((sum(Volume)/sum(total Volume))*100)+1,’##.##’)&’>’

And there you have it the basics of how to create a Dynamic Network Flow Chart. There are obviously a few more little tweaks that can be added to aid the visuals, for instance I’ve added a second chart behind the Line Chart that has small datapoints to further aid the pin pointing of the offices. I’ve also added a ‘Values on Datapoints’ expression to allow for Office names to be shown on the chart which is controlled via a Windows Checkbox:

As a further enhancement I’ve added some regional labels that show the total for that region as well as what % it accounts for, these are smart labels as they only appear when there’s actually information to show; why have a label when no business was written from that area?, the absence of a line shows that. I’ve also added a button to clear only the geographical selections to aid usability.

As I’ve made pretty clear I really like this chart, I don’t like the majority of the charts that I create (or see), they feel to have something missing; they don’t show things as well as they might or they’re needlessly fancy (I’m hyper-critical of my own work) but I think this finds the balance between being visually engaging and resonant whilst also driving insights that wouldn’t be gained otherwise. We could of course push things further and create a true network that shows flows between seperate datapoints and not just to the HO, I’d imagine the method for that would use some of the elements employed in the Radial Chart I posted a few months ago: https://qvdesign.wordpress.com/2012/03/05/associative-radial-chart/, I see many possibilities for this chart techique and can’t wait to apply to other appropriate datasets.

I hope you can put it to use and as always; all the best,

Matt

The .qvw for the chart can be downloaded from here: https://docs.google.com/open?id=0BxloTMUod74tQ3dCSHhybnlmY2c

*All data in the .qvw is sample test data.

Advertisements
Comments
17 Responses to “New Qlikview Chart Type: Dynamic Network Flow Charts”
  1. Richard says:

    Yet again you’ve produced something truly stunning. Great work Matt!

  2. Lewis says:

    Hats off to you! That’s awesome!

  3. Adriaan says:

    Hi Matt,

    Truly genius, you’re really on a roll, your previous post was also great.
    For the tedious task of giving in the coordinates, wouldn’t it be possible to use the long and lat coordinates and to recalculate this to something that would work for the x and y axis.
    Once I have some time, I’m planning to look into that.
    You can use a formula like (point a + point b)/2 as for the point in the middle.

    Cheers,

    Adriaan

    • qvdesign says:

      Adriaan,

      Thanks for the comments.

      I did consider trying to use the true long / lat values to generate the chart but thought the image and reality almost certainly wouldn’t line up. If only Googlemaps had a good ‘world’ image.

      All the best,

      Matt

  4. pablo mr says:

    Great job!! and really useful. Thanks for sharing your creativity with us.

  5. Perumal says:

    Hi Matt ,
    Great Job……i have one doubt .how calculate x and Y Coordinate and what use of Type means Location and HO and Curve.

    • qvdesign says:

      The X & Y co-ordinates aren’t calculated they’re manually entered. As it’s a Line Chart we need 2 data points per Office to create a line and 3 to generate the curve, the ‘Type’ allows us to control what is displayed at each datapoint, for instance the labels are only shown when Type=’Location’.

      All the best,

      Matt

      • Perumal says:

        Hi,
        Thanks for Reply.
        How know position wise x & Y co-ordinates for manually enter data,otherwise any value enter for X and Y co-ordinates,.

      • qvdesign says:

        Your X & Y axes both run from 0 to 100. If you want to position say Johanesburg in South Africa then by looking at the map it’s about half way along the X Axis and a third of the way up the Y so you’d have X = 50 and Y=33. Reload the data, see where the point is, if it’s not located properly adjust the X & Y values manually to position it as required through trial and error.

        All the best,

        Matt

  6. Marco b says:

    A stunning creation that truly represent information in a beautiful way.
    Thanks for sharing.

  7. Steve Dark says:

    The implementation of this is beautifully simple – but the result is stunning. Thanks for posting.

  8. Jeremy says:

    Excellent work and inspiration!

  9. QVb says:

    This is amazing. Can you clarify whether the color coding on the line can be formatted so that for certain volumes a Red, Yellow or Green format can be presented vs. the default fuscia color? I am relatively new to QV but can’t seem to figure out where I would apply this conditional color coding and if it is possible.

    Thanks so much!

  10. Perumal says:

    is possible find out position wise x & Y co-ordinates in Script based on Longitude,Latitude
    Thanks.

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: