//In the previous blog, we looked at how consistency is important throughout a set of reports and dashboards. It helps users to understand the reports quickly and easily. Another important aspect to maintain is scenario handling; this forms the UNIFY part of IBCS’ SUCCESS formula.
Continue reading “Lessons from IBCS – Scenario Handling”
//When reading reports and dashboards, we often find ourselves spending more time than we’d expect analysing the charts and tables tying to comprehend the data. In many cases that’s due to inconsistencies in the presentation, or poor design. Ideally a report should convey the message clearly and quickly.
One of the underlying rules of IBCS is maintaining visual integrity; this forms the ‘Check’ component of IBCS’s ‘SUCCESS’ formula. It requires reports and dashboards to present information in the most truthful and easily understood way. This involves avoiding misleading visuals including improper scaling and manipulated representations. When interpreting data, information that looks the same should also mean the same. Consistency is important and helps users to understand the report quickly and easily.
Continue reading “Lessons from IBCS – Maintaining Visual Integrity”
//The core job of a chart is to convey information to the viewer as clearly, concisely, and quickly as possible. One thing that can detract from this is chart clutter – distracting or unnecessary additional elements which can mask the overall message.
Continue reading “Lessons from IBCS – Eliminating Chart Clutter”
//Choosing the right chart type to display your data is key to communicating an understandable message to users. Bad or inconsistent use of chart types can lead to confusion and poor interpretations of the data. For a report to be effective, each visualisation must be carefully and deliberately chosen.
In this post, we will outline some of the guidance on chart orientation and type given by IBCS.
Continue reading “Lessons From IBCS – Choosing The Right Chart Type”
//One of the biggest improvements in 9.2 is undoubtedly in the area of interactive charting. We’ve hugely extended the capabilities of Small Multiples through a new charting engine which brings rich interactive Visual Analytics to Excel (and web, and mobile…).
The ‘Small Multiple’ concept of many charts with a shared axis is very powerful, but in some cases users just need a single interactive chart and 9.2 caters for both scenarios. We have added zoom controls, sliders and a play axis to help users quickly focus in on and further explore specific areas of interest within the chart.
//Repeaters are a visualisation feature introduced in v9.1. They are effective when you want to repeat a formatted section of a report by one variable. They can save so much time as you don’t have to go through the tedious, error-prone task of recreating the same section many times by copying and pasting manually. Imagine the time you’d save setting the design up just once and have the repetition handled by XLCubed!
//This summer English Premier league clubs spent more than ever before on player transfers, a staggering £1.47bn in total. Some spent a lot more than others, and while PSG are making the Financial Fair Play headlines globally, the EPL clubs as a group spent more than any other league.
There are lots of ways to analyse spending, and rather than write a detailed analysis or opinion piece (as I’d doubtless end up being biased), I’ve taken the opportunity to simply present the transfer activity in a few different visualisations and readers can draw their own conclusions. Continue reading “Charting the Premier League Transfer Window”
//Well, the Rio 2016 games have finished and we now all need to find something else to watch on TV. As always at the Olympics there was plenty to entertain and inspire. After the London games in 2012 we blogged showing the medal distribution using Treemaps. We’ve updated that for 2016 below with the corresponding 2012 equivalent:
The charts are split by country, and then sport where the size of the tile represents total number of medals, and the colour saturation represents the number of Gold medals. We can see immediately that the US retains a significant lead over the other nations, and also that roughly half its medals overall were won in Swimming and Athletics. Great Britain and France have seen their relative medal positions strengthen in the four years. It’s difficult to see the breakdown for countries with smaller numbers of medals, but the interactive version can of course be drilled to additional detail and we’ll make that available in the coming weeks.
Looking at things split by Sport then by country it’s as below:
Athletics and swimming have the most events and hence the most medals and largest presence on the Treemap. USA dominates both categories across both London and Rio, with an even stronger grip on athletics in Rio. Elsewhere China rule the diving boards, winning 7 of the 8 events in Rio.
Team GB again did spectacularly well in Rio, and as a British company we can allow ourselves a slight bias in our coverage (a roundabout way of saying the remaining charts are just about the British team). Firstly we’ve brought the 2012 and 2016 data for GB together into one treemap as shown below.
While the mix of sports is slightly different, and in both games the team won medals across 19 sports, the core strengths remain fairly consistent. Despite that, there are some interesting movements. Gymnastics and swimming have shown the biggest improvements between 2012 and 2016. Cycling (all cycling disciplines grouped) had the same number of medals in total, but 2 fewer gold. Having said that when you start from such a high base even being close is success – when other teams are videoing your warm up / stay warm routines it’s safe to assume you’re doing something right!
Last but not least, a column chart showing overall GB medals by discipline across the two games – if you need a binary sport by sport comparison rather than contribution to total the classics still tell it best.
//A common question that comes up in support for XL Cubed is how to add charts that look like a dial, or a gauge. Something like the below:
These are actually very easy to make and publish to the web, plus they have the further bonus of adding something different to make your reports look more professional.
Once you have your data ready, add a new doughnut chart and configure it to show the information you want it to.
This will give you a simple doughnut chart.
Next up, pick the cell that contains the information you want to show in the middle of the doughnut chart and reference it in another cell. For example, in the below example we have the two numbers that make up our doughnut chart in cells B3 and B4. Cell E3 contains the information we want to show in the middle of the doughnut chart.
As you can see, the formatting is different in E3 to the other cells. This is because we have formatted the cell to show the data how we want it to appear in the chart.
Once we are at this stage, it is just a case of transferring the number to the middle of the doughnut chart. You can do this by selecting the formatted cell, in our case E3, copying it and then paste special as a ‘Linked Picture’ anywhere in the worksheet (we will move it into the chart in the next step).
The ‘Linked Picture’ appears as a cell but it actually acts like a picture so, lastly, move the picture into the middle of the doughnut chart so it looks how you want it, then, right click on the new picture and select ‘Send to Back’
As the cell is a ‘Linked Picture’ Any changes you make to the cell you copied, formatting or data, will update the image.
Your Gauge Chart is complete! These charts also look good when published to the web.
//So today’s blog is about adding Bump Charts in Excel using v8 XLCubed.
Initially a Bump Chart looks the same as a line chart – the difference is they plot the rank position rather than the actual value.
Let’s imagine that I sell a product in a marketplace with 10 other competitors. I may like to see how the rank position of my product and the competition changes over time to check if I’m gaining or losing market position. It’s a common scenario in pharma, where we have a good customer base.
You will usually want dates on the category axis so the trends are shown across time. The series then holds the items to be compared, in this case the products.
Our example has been set up with Measures on Headers, Product Categories on Series and Date Calendar on Categories. For more information on using Small Multiples in XLCubed please visit Small Multiple Charts.
The currently selected measure is Reseller Order Quantities (selected though the Measures slicer)
for the eleven months prior to April 2008 (selected through the Date slicer)
for a subset of products.
Looking at the bump chart you can see that I’ve selected Road Bikes and Mountain Bikes for easy comparison. You can quickly see that the rank position for Road Bikes dropped quite dramatically from May 2007, picked up again in September before dropping again in November and rising in December through to February 2008. The change for Mountain Bikes, on the other hand, was less dramatic, rising and falling slightly, steadying in February 2008 before dropping again the following month.
To create a bump chart just select Line – Bump as the Chart Type on your Small Multiple chart. The neat part is that all the rankings are worked out for you behind the scenes, without the need for lots of complex Excel gymnastics trying to work through the full result set month by month.
