This chapter is from the book
This chapter is from the book
This chapter is from the book
Working with XY and Bubble Charts
Bar charts, column charts, combination charts, line charts, and a few other variants are charts that are continuous on one axis and discrete on the other. This is fine for histograms and the like, but it offers little benefit when you need both the horizontal and vertical axes to be continuous. Xcelsius 2008 provides two kinds of continuous charts: XY and bubble charts.
Each data series in an XY chart houses data for a range of values along the X-axis and a corresponding range of values along the Y-axis. This affords a lot of interesting possibilities.
Working with XY Charts
Suppose you have some raw data on individuals’ years of education and age (see Figure 5.18).
){kind=link}
The tabular data to the right of the chart is just a small segment of the full dataset. An XY chart gives you the ability to specify a number of features of your data series, including the series shape, fill color, marker size, and transparency (see Figure 5.19).
)
Figure 5.19 Customizing the data series appearance in an XY chart.
XY charts can display only two sets of values at any time—one on the X-axis and the other on the Y-axis. However, you might have a multitude of factors from which to select. It would be great to start from a list of parameters—such as age, income, and education—and choose which two go onto the XY chart. There are three ways to do this:
- Using a List Builder component
- Using naive lists
- Using intelligent lists
At first glance, using List Builder would appear to be the natural way to do this. You may have 10 or 20 kinds of parameters that you want to make available for plotting on an XY chart. With List Builder, it is easy to choose more parameters than an XY chart can accommodate (see Figure 5.20). In such a case, the extra parameters are ignored. One thing you don’t want to do is to surprise a dashboard user by inadvertently withholding information he or she expects to see.
)
Figure 5.20 List Builder doesn’t stop you if you select more than two parameters.
There are a couple other reasons to avoid using List Builder to create an XY chart. List Builder copies data to a location. If the original data changes, the changes are not reflected in the chart until the List Builder is updated. In addition, any time the user wants to switch which items appear in a chart, he or she must go back to the List Builder chart and reconstruct the list. List Builder may be indispensable for constructing reports, but it does not always provide the fluid interactivity needed for dashboards.
Another approach would be to supply for each axis a list-like selector such as a List Box or Radio Button component and, based on the parameter selected, look up the respective dataset. This technique overcomes the primary challenges of using List Builder: It is not possible to oversaturate the XY chart with too many parameters, and there is no wait time; as soon as an item is selected from the list, the data appears on the plot. There is one wrinkle with using a List Box or Radio Button component, though: It is possible to select the same item in each of the independent lists. For instance, it is possible to plot income on both the X-axis and Y-axis. Although this is not problematic, it isn’t very elegant.
You can use a strategy that automatically eliminates the item chosen from the list (see Figure 5.21). This strategy involves what I call correlated lists. There is a list for the X-axis and one for the Y-axis. Notice in Figure 5.21 that the X-axis list box has three items, and the Y-axis list box has two items. Also notice that the item selected in the X-axis box is conspicuously absent from the Y-axis box. This is by design. No matter which item is chosen in the X-axis box, it is automatically eliminated from the Y-axis box.
)
Figure 5.21 You can select parameters in the XY chart by using correlated list boxes.
Let’s look at some implementation details. Your List Box component should be based on inserting values, not position (see Figure 5.22).
)
Figure 5.22 List box properties for the X-axis.
In cells C3, C4, and C5, you need to place the value 1, 2, and 3 (see Figure 5.23). In cells C6, C7, and C8, you need to place the labels Yrs education, Age, and Income.
)
Figure 5.23 Spreadsheet setup for correlated list boxes.
Your list boxes for the X-axis and Y-axis should be reading the labels from your underlying spreadsheet (cells C6:C8 and D6:D7).
The formulas for cells D3 through D7 get a little complicated. I leave you to explore these on your own in the file ch05_XYChart.xlf. Essentially, the logic behind them is that if an item was already selected for the X-axis omit this item for the Y-axis and go to the next item in the list.
To complete the picture, the datasets that are chosen (the shaded cells on the right side of Figure 5.24) are retrieved for display in the XY chart (the left side of Figure 5.24).
)
Figure 5.24 Datasets are chosen for graphical display.
In this example, you can choose any 2 of 3 data sets. There is nothing to stop you from setting up your dashboard to select from, say, 20 possible datasets. Unlike using the List Builder approach, with this method, the retrieved data is still live. Changes to source data for the values plotted are instantly reflected in the chart.
Extending Graphical Presentation with Bubble Charts
The bubble chart can be regarded as the sibling of the XY chart. The essential differences between the two are that in a bubble chart, the marker size is variable, based on the value of some data, and the marker shape is round. Bubble charts offer a convenient way to pack more information into a chart. Rather than being forced to choose two of three parameters, you can simultaneously display all three in a single chart. With a bubble chart, you need to decide which parameter is associated with the X-axis, which parameter is associated with the Y-axis, and the bubble size (see Figure 5.25).
)
Figure 5.25 You can choose bubble chart parameters from the list boxes.
Bubble charts use size to represent a quantity. So how would you represent a negative quantity with size? Does a circle implode in on itself and invert its color? Xcelsius 2008 does not provide a particularly elegant solution for negative values. Basically, it shrugs its shoulders and gives you a little dot that is non-changing in size.
Fortunately, there’s a workaround that allows for a relatively clean implementation. The setup is quite simple. You position your data to display your X coordinates, Y coordinates, and bubble size (see columns B, C, and D in Figure 5.26).
)
Figure 5.26 Setting up a bubble chart to support negative values.
The next step is to separate positive and negative sizes (columns E and F of Figure 5.26). The respective formulas in columns E and F could be something like this:
=IF(D2>=0,D2,"") positive values in column E =IF(D2<0,D2,"") negative values in column F
Next, create a data series for the positive and negative values (see Figure 5.27). (You’ll learn the details behind the halo sensors shortly.)
)
Figure 5.27 Properties of the data series detailing negative values.
In the Appearance tab, set the color of the positive data series to something like green and set negative values to red. Choose whatever colors suit your needs.
In this particular example, all the data is static except for a single data point, whose size can be adjusted with a slider to both positive and negative values. The X and Y coordinates for this data point use formulas that incorporate the size. Consequently, the data point moves as you adjust the slider, and the chart automatically rescales. This example is a little contrived, but it helps to make the essential concepts and their implementation clear.
Displaying Values of Individual Data Points
At this time, Xcelsius 2008 does not natively support the display of negative sizes. Negative values for bubble size are rendered as tiny dots that never change size. In the preceding section, you used a little trick to fool Xcelsius into treating negative size bubbles as if they are positive and at the same time, change their colors. This size/color combination renders correctly, but Xcelsius still thinks the bubble size is a positive value. When your mouse hovers over the “negative” size bubble, the hover text displays the correct X and Y coordinates, but the negative value for size shows up as a positive number.
Rather than deliver a broken dashboard, it is better to turn off the mouse over text. But don’t despair. I know another useful trick. By using the drill down capability of the bubble chart, it is easy to extract which point the mouse is positioned over and push relevant data about the point, including the negative bubble size, to a table that’s suitable for displaying the data. I call this technique the halo sensor. With this method, you surround each data point with a thin ring, or “halo,” that is capable of sensing when the mouse is positioned over it. It uses the drill down feature of the chart to identify which data point has focus. When the data point is identified, its related information can be easily retrieved.
The halo is set up as a separate data series. It visually appears behind the positive and “negative” size circles. If a halo were smaller than or the same size as these positive or negative circles, it would be eclipsed. Consequently, the halo needs to be a larger size than the circle size for the data points. Doubling the size seems to work well.
When you enable drill down, you may want to set the interaction options to mouse over instead of mouse click. Incidentally, you can drill down with each of the series as long the insertion points do not overlap.