Category Archives: Charts

How to gauge data through charts – Creating Gauge Charts

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:

1

 

 

 

 

 

 

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.

2

 

 

 

 

 

 

 

 

 

This will give you a simple doughnut chart.

 

3

 

 

 

 

 

 

 

 

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.

4

 

 

 

 

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).

5

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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’

 

6

 

 

 

 

 

 

 

 

 

 

 

 

 

 

As the cell is a ‘Linked Picture’ Any changes you make to the cell you copied, formatting or data, will update the image.

7

 

8

 

Your Gauge Chart is complete! These charts also look good when published to the web.

 

Report Flexibility, with Control

Sometimes we want to let report users modify the structure of a report but to govern exactly what they can and can’t do. While Grids can be restricted at a granular level to enable and disable functionality, that approach still requires some degree of product knowledge by the user.

XLCubed provides the XL3SetProperty() formula, which enables manipulation of many of the core objects such as Grids, Slicers and Small Multiples. It means report users can have simple slicer selections to change the structure of a report, what’s being displayed in a chart, or to vary the chart type. It gives flexibility within the report, but requires no product knowledge from the end user which can be crucial when delivering web reports on a widespread basis.

One common example of usage is where the hierarchy to be viewed in a grid needs to change based on the measure a user selects (depending on the structure of the cube some measures may not be applicable for all hierarchies). Typically that would need to be handled in two Grids, but we can use XL3SetProperty to bring this together, and also to give user choice on the associated Small Multiple Chart view.

The final published report is shown below:

 

S1

 

If the user selects an “Internet” measure, we show Customer Geography on rows, whereas a “Reseller” measure should show Reseller Type on rows. The same logic applies to the Small Multiple chart. In the screenshot below, the user has selected Reseller Gross Profit as the measure, and ‘Stacked Column’ as the chart type. You can see that the hierarchy on rows has been switched, as has the split within the individual charts, allowing the user to easily vary their view of the data with simple button selectors.

 

S2

 

This is implemented through the following key points:

  • A lookup table in Excel to determine what hierarchy is applicable for each measure
  • An Excel list showing the available chart types – this is used in the Chart Type slicer
    • The chart slicer outputs its selection into cell $AG$10
  • The measure slicer is linked directly to the grid and the small multiple, but also outputs its selection to an Excel cell ($A$B4)
  • A vlookup determines which hierarchy to use based on the selected measure
  • Three XL3SetProperty() formulae now control what is displayed based on user selections:
    • $AB$7 – sets the grid rows
    • $AB$8 – sets the small multiple columns
    • $AB$7 – sets the chart type

 

Formulae

 

The approach gives a deep level of access to the key XLCubed reporting objects, and enables controlled flexibility within web and mobile-delivered reports. No programming is needed, just a mid-level understanding of Excel itself, and XLCubed.

This is just one example of what the approach can achieve – it’s really limited only by imagination. See XL3SetProperty() for more detail, or contact us if you’d like the example workbook.

Bump Charts in XLCubed

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.

BBC1

 

 

 

 

 

 

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)

BBC2

 

 

 

 

for the eleven months prior to April 2008 (selected through the Date slicer)

 

BBC3

 

 

 

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.

Excel heat maps made easy!

With the recent release of version 8 we’re going to blog about a number of the new features, starting with how to create a heat map in Excel.

Here’s a fairly large table showing sales for thirty six products across twenty six US states:

 v8B1

There’s a lot of data here but it’s not giving us any helpful information as the table is too large to see any pattern or comparison.

A heat map could be a useful way to give a quick visual picture of the spread of the sales volume. Let’s add a simple heat map, new in version 8 of XLCubed.

Select the data area in the table, and then from the XLCubed ribbon select the InCell-Chart group, and heat map:

v8B2

 

As we have already selected the data area to be charted this prompt is already showing the correct cell locations.

Choose the formula destination (where the formula controlling the chart will be located), and the Chart destination (where the top left cell in the chart area will be located).

We can now define the look of the heat map in the Chart Format dialog:

v8B3

 

 

We have set the low and high colours to define a blue colour gradient.

Outlying values could potentially skew the chart so you have the option to exclude these by setting minimum and maximum values.  Select the icon to use, squares in our case, and the number of steps or bands to split the range of values into.

We have pre-arranged the Excel cell sizes to be squares, and this is the resulting heat map:

v8B4

 

You can now quickly assimilate the spread of values in a glance, and note the higher sales volumes in Maine, Michigan and Missouri for Road, Touring and Mountain Bikes.

To alter the formatting of the chart simply double click on any one of the squares in the heat map, or on the chart formula to bring up the formatting dialog.

If you are not already a user of XLCubed you can get started with an evaluation of XLCubed by going to our registration page.

Excel BI myths debunked – #6: No report sharing & distribution

Here we continue our theme on the myths which get propagated about Excel based BI. The next argument is that Excel BI cannot handle widespread report sharing and distribution. Base case we actually agree with this one, and that’s why we invested in developing XLCubed Web Edition specifically to address it.

Understandably, sharing an Excel workbook around hundreds or thousands of users is not something which many companies will consider. A web based distribution approach is much lighter and easier to manage. The drawback is that most web based report design environments lack the flexibility and latent user skill base of Excel. XLCubed provides a simple way to push data-connected reports developed in Excel to a portal based environment, where report consumers don’t require any software installed locally, other than a browser. The reports can also be accessed interactively through our native mobile apps for Apple, Android and Windows phone 8.

XLCubed Web is self-sufficient and does not require SharePoint. For customers with SharePoint and keen to retain it as a centralised environment – no problem, XLCubed Web can integrate so tightly within SharePoint the end users won’t even know it’s there.

Excel based users can become web and mobile report designers in minutes. XLCubed uses Excel as a key part of the BI solution rather than as the entire BI solution, and it’s that which allows us to address the sharing problem, along with the other myths we have identified in this blog series.

 from any version of Excel:

ipxl

…to web…

ipwb

…to mobile.

ipip

Some Excel BI myths debunked #2: Inflexible Charting

#2: Inflexible Charting

Continuing our discussion of common criticisms of Excel focused BI, let’s take a look at charting.

“Excel charts are static, inflexible and you need to start from scratch if you want to change them.“

The flipside is that everyone knows how to use them, and in reality many charts in business reporting are in effect static – the numbers being charted change, but the chart layout and number of elements being charted stays the same.

Of course there are cases when charts can vary considerably with the data, or perhaps you would like to be able to drill into more detail on the chart, or to quickly display multiple charts split by one variable. Excel charting can’t handle those scenarios, but XLCubed caters for it through Small Multiples.  The example below depicts river water quality in different regions of England. It could be built in native Excel, but would be a painful and time consuming process. With XLCubed it’s a drag and drop process in our small multiple designer.

waterqualitysmalt

If the number of regions being reported changes, the number of charts being plotted will automatically stay in sync, and there is a direct data connection rather than having to maintain Excel ranges etc.

Sometimes with charting small is beautiful. Perhaps we just want the key numbers with a Sparkline alongside, or a bullet graph or bar chart to display actual to target. Native Excel 2010 and 2013 can handle the Sparkline, but not the ability to then drill the report and have the Sparklines extend, and there is also the issue of needing to bring the data itself into Excel before charting it.

XLCubed Grids can contain dynamic in-cell charts which build the charts as part of the query, and as such are drillable and remove the need to maintain a data range in Excel as shown below.

DrillIncell

So XLCubed brings the type of dynamic charting being described to Excel, and provides a simple web and mobile deployment option.

 

 

Bandlines in XLCubed

In early January this year Stephen Few introduced the concept of Bandlines. He identified a useful extension to Sparklines, making use of shaded or coloured horizontal bands to provide more information on the context of the trend line itself. See Stephen’s article on Bandlines and the thinking behind them for a detail description.

The Sparklines are ideal for showing individual trends in a small amount of screen real estate, and we use them extensively in dashboards, typically in a ‘visual table’. By definition Sparklines are small, and to make the trend easily readable, they are typically scaled individually so that each Sparkline uses the whole vertical axis. This means they do not give any impression of the scale of the numbers involved across different rows. It’s possible to use a common scale, and while sometimes that works more often it means many of the rows with smaller values are excessively flattened.

Bandlines address this by introducing horizontal shaded areas depicting the lower, middle and upper quartiles and the median represented by a line. The user can determine the context of the bands. The two most common examples would be plotting recent trend in the context of a longer period, or plotting individual rows in the context of the overall set of data being displayed.

We think Bandlines add real value, so hats off once again to Stephen, and we’re pleased to say that Bandlines are now available in the current version of XLCubed (see here for more detail).

The screenshots below show two examples, displayed in two colour schemes.

 

Bandlines3

The charts depict historic margin by store. The ‘Banding across all stores’ charts show the 30-day trend for the individual store, set in the quartile context of the data for all 11 stores in the table. We can see that for the Gilroy store in row 1, while the margin has varied, it remained in the top quartile when set against all stores for almost the whole period.

The ‘Banding by store, 90 days’ charts show the individual 30 day trend, set in the context of the previous 90 days for the individual store. This helps provide much more historical context, but the line itself still focuses on the more recent trend. Stockton is probably most noteworty here as across the 30 day period it has dropped from the top quartile into the 1st quartile across the whole 90 day period.

We’d love to hear your thoughts (and also which colour scheme works best!), we will also be adding Sparkstrips in the near future so watch this space.

 

 

 

 

Excel Pareto Charts the XLCubed way!

V7.2 of XLCubed is released soon and we thought we’d take the opportunity to run through one of the new features that you’ll be seeing, Pareto Charts.

The Pareto Principle is often referred to as the 80-20 rule, that 80% of outcomes are attributable to 20% of causes. They are named after Vilfredo Pareto who lived in Italy in the 19thcentury and observed that 80% of the land was owned by 20% of the people.   Pareto charts have both bar charts and a line graph where the bars represent individual values and the line represents the cumulative total.

So how do you use Pareto Charts from XLCubed?  Very simply, within a grid you right-click on the column header to access XLCubed’s right-click menu, Grid Charts and Add Pareto Analysis.

Take this simple grid showing Reseller Sales for Product Model Categories for Canadian cities:

Right-clicking on All Products to Add Pareto Analysis brings up this window:

Click OK to return to the workbook – you will see that we have a chart showing that the top 9 cities provide some 80% of the sales.

You could also include the rolling total and percentage in your Pareto Chart.

Notice that we now also have some extra columns on the grid showing the cumulative total of all sales, the sales percentage per category and the cumulative percentage.

 

 

So that’s Pareto Charts – in a nutshell, an easy to use graphical tool which ties directly into dynamic XLCubed grids.

Olympics Treemap

The 2012 London Olympics have now finished, and as a UK company we were pleased to see the games were such a success, and of course that team GB did so spectacularly well! We’re looking forward now to the Paralympics in a couple of weeks, and once the dust has settled there we’ll be shipping a new point release of XLCubed in September.

We’ll keep most of the changes under wraps for now, but one item which we are introducing is treemaps. The Olympic medal table gives us a nice opportunity to better understand the medal breakdown through the  new chart type. In XLCubed, treemaps can be produced directly from a cube or from a table held in Excel, as is the case here. The first example below shows the medals split by country and sport. The size of the rectangle depicts the total number of medals, and the colour shows the number of gold medals, the darker the colour the more gold. The numeric values list the total number of medals, then the number of golds. We can see the USA at the top, and that over half their medals came from swimming and athletics, with a bigger percentage of golds in the pool.

Any of the countries can be drilled into for a large view on their medal breakdown, not that we’re partisan of course… , but the view below is for Great Britain (GBR) where the particularly good showing by the cycling team stands out.

Taking a look at the same data split first by sport and then country, it’s easy to see the countries dominating the medals in each sport, and to delve into more detail by sport where required.


 Drilling into Athletics we can see that USA won most medals, and also most gold. Great Britain had just the 6 medals, but 4 were gold and hence the darker colour on their tile.

We’ll be making an interactive version of this available over the next few days.

 

 

Small Multiples on River Quality

The phrase small multiple was popularised by Edward Tufte, and has become a generic term for a visual display using the same chart or graphic to display different slices of a data set. Their close positioning and shared scale make comparisons very easy and shared trends or outliers can be quickly spotted. Various other terms are also used to describe this charting approach, or specific aspects of it, including Trellis Charts, Lattice Charts, Grid Charts and Panel Charts.

The most common use case for small multiples is separate line charts to compare trend across a large number of varying elements. Placing them all within one chart would cause either a ‘spaghetti chart’ , or lots of occlusion as shown in the comparison below. Here we use a standard Excel line chart, and an XLCubed small multiple to chart the same data. Separating the charts while keeping a consistent axis scale makes for a much easier comparison than in the single chart.

We took a slightly different approach when using small multiples to take a look at differences in river water quality across regions of the UK. Our source data was not absolute numeric values, but 14 years of results categorised into four bandings (bad, poor, fair and good). We wanted to provide a ‘one-pager’ which gave a feel for the trend within each region, but also access to the annual breakdown of the different water qualities.

In the end we settled on a Small Multiple display of 100% stacked columns as shown below.

A percentage base seemed a sensible way to approach the data, as different regions will have differing numbers of rivers and of samples taken. Using this approach we’re able to see a comparison of the relative water quality rather than dealing in absolutes.

The user selects a geographic area of the country to view the regional breakdown within the selected area. The water quality for a particular year can be analysed by locating the region, and the specific year to see the percentage breakdown for each of the four categories.

The colouring of the 4 categories was chosen to aid ‘at a glance’ recognition of the overall water quality by region, and also of the trend. Dark blue signifies bad quality water (opaque), and light blue signifies good quality (think ‘you can see right through it….’).

So to read the display overall, or for trend:
• Dark colour signifies water quality problems.
• Light colour signifies good quality water.
• Reading left to right, increasing colour saturation shows declining quality over time.
• Reading left to right, decreasing colour saturation shows improving quality over time.
• Any region can be zoomed in on to see a larger chart and understand the breakdown in more detail.

Fairly quickly, and from just this one display we can draw a number of conclusions as below:
• Across the region, as a broad brush summary, water quality has improved since 1992.
• Doncaster has shown strong and steady improvement.
• Kingston upon Hull has the worst quality overall in the region, and varies significantly year on year.
• If you’re off for a swim in a Yorkshire river, Richmondshire looks a good bet!

We’ve designed a pre-set view in this case to work for the data in question, but the small multiple concept is also very powerful when interactively exploring data. A picture can tell a thousand words as they say – take a look at our youtube videos on small multiples: Video1 Video2