Overview

Both Excel and Xcelsius have Bubble Charts. These are similar to X-Y Scatter Charts, but rather than plotting a point of data as a point, a circle is displayed centered about an X-Y coordinate. The radius of this circle corresponds to the value in a data series. The radius or circle size can signify anything we want, be it sales trends, risk metrics, whatever.

The appearance of a group of circles in a chart can sometimes look like "bubbles", hence a Bubble Chart. Most of the time bubble charts are associated with displaying data on an X-Y grid (see Figure 1).

Figure 1: Bubble Chart on an X-Y grid

The gridlines in the chart helps to interpret information, especially when it is quantitative and complex. But sometimes, those horizontal and vertical lines create visual clutter. It may be simpler to do away with chart gridlines, and even the background; just place the chart on top of a picture.

It may be simpler to do away with chart gridlines, and even the background; just place the chart on top of a picture.

Creating the ETC BubbleMap

The first step is to start with the data you want to chart. Sample data could include information for various data point you may want to plot over a timeline. The data could be anything like census recording of population, product sales, or housing foreclosures (see Figure 2). For illustrative purposes in this article, population data is used.

Figure 2: Population by state on a timeline

To put this kind on information on a bubble chart, you need to do two things:

• You need to be able to choose data for a particular year or interval in the timeline. The selected data becomes determines the "bubble size" or radius for each of the data points.
• You need to position each point by providing X-Y coordinates. Ostensibly, you could use longitude and latitude as the x and y values.

Your spreadsheet needs to end up looking something like Figure 3.

Figure 3: Assemble data for plotting

To retrieve the data for cell D3, it's a matter of performing a lookup of cell D1 with a formula like:

=INDEX(F3:AF3,D$1)

Then replicate this formula down the column.

Of course, when you plot your data, you need to rescale the x and y coordinates so that the plotted points are correctly positioned over the visual map (see Figure 4).

Figure 4: Positioning a Bubble Chart on top of a map

Timelines: Making the BubbleMap Interactive

To make the BubbleMap interactive, you can use a slider component to pick which slice of data you want to view (see Figure 5).

Figure 5: Using a slider to select which year you want displayed

Sometimes it may be essential to pick more than one period of data. You might, for instance, want to be displaying a trend over a range of dates. There are several ways to accomplish this. One of these is to use a dual slider. The other approach, which is used here, is to use a single slider and have a separate slider for spacing the interval (this is the vertical slider in the dashboard).

To add to the interactivity, you can enable drill down of your data into a separate chart, such as a column chart (see Figure 6).

Figure 6: Clicking a state capital pushes the state timeline information to a column chart

In the example shown, you could click a state capital of Oklahoma (which is displayed in the inset of Figure 6). This pushes the Oklahoma data onto the column chart for the full range of years. Notice the portion of the chart highlighted in gold. These are used to compute the Compound Annual Growth Rate or CAGR (see Figure 7).

Figure 7: Compound Annual Growth Rate at both the national and state level

Figure 7:  Compound Annual Growth Rate at both the national and state level

The gold bars in the column chart of Figure 6 can be shifted across the timeline by either dragging the horizontal slider component or by clicking any of the gray bars in the column chart.

The gold bars in the column chart of Figure 6 can be shifted across the timeline by either dragging the horizontal slider component or by clicking any of the gray bars in the column chart.

Incidentally, the formula for computing the CAGR is

=(ValueAtTime2/ ValueAtTime1)^(1/N)-1

Other features worth noting

There is no need to limit the number of map layers to one. In the dashboard shown, you can toggle between showing and hiding topography.

In the BubbleMap, periods of population decline are shown in red.

We thought about adding other features like enabling animation on the horizontal slider. We felt that it would create too much distraction, by shifting focus away from understanding the data.

Closing Thoughts

It is lots of fun to build a BubbleMap, but it is not always simple to build. A lot of the time is spent on appropriate design, so information can be meaningfully displayed at a glance. Conveying meaningful information at a glance is the objective of a dashboard.

Positioning the data points on a map is best achieved using actual map coordinates and careful mathematical transformations, and not by hand editing (which is slow and prone to error).

©2007 Evolving Technologies Corporation - all rights reserved.