December 2001

Vol. 3, No. 12

More than one way to skin that cat

We may be surrounded by data, but unless we look at that data in ways that will give information, we miss an opportunity for meaningful analysis. Sometimes this means looking at data in a variety of ways, to elicit diverse and useful information.

Data, data, everywhere: It's easy to find interesting and chart worthy data in unexpected places. Imagine, for example, a simple spreadsheet or database containing three columns:

Id Date Status
1 01/01/01 Red
2 01/02/01 Green
3 01/02/01 Blue
4 01/03/01 Blue

This data can give up other information, if it is massaged and examined, but first you must be willing to spend some time understanding what you need. Instead of Status, perhaps your data contains a column such as Outcome, or Rating, or something else. At first glance, you might think there is not much here for making a chart. A simple pie chart using the Status column might be interesting, however. For example:

click to view larger image

This may not yield much information, but after all, it is a chart, a visual representation, rather than just columns of numbers. Look again at the data. Can you think of a way to make a control chart? Without much raw data to use, one may have to get creative and make a query. Of course, unless you know what you want from the data, you will not know what questions to ask, so it is essential that you think this through first.

Queries facilitate the process of looking at the same pile of data from a different perspective. A query requires no re-typing, importing, or exporting. For example, it might be interesting to know how many records or cases are handled each day. By using a query, you can get the following chart showing number of cases per day:

click to view larger image

With only a slight variation on this theme, a different query would give you a control chart showing the number of cases handled each month:

click to view larger image

With these two charts, information about the mean number of cases handled each day and the mean number of cases handled each month is elicited.

Next, consider that when Status is Red, this represents a defect or some other undesirable state of affairs. In this case, you might want to make a p-chart or a percent defective chart. Once again, this demands a clever query in order to know two different numbers for each day: a) the total number of cases, and b) the number of cases where Status is Red. A new query gives us the following p-chart:

click to view larger image

With only a slight variation on this theme, you can do a p-chart focusing on the number of blue cases each day:

click to view larger image

If you were interested in seeing how the Status categories change from month to month, yet another query would allow you to create a chart like this:

click to view larger image

Finally, to summarize this data at the annual stockholders meeting, you might make a multi-chart showing ALL your clever analysis of what started out to be some pretty boring data:

click to view larger image

These charts were created using CHARTrunner. The original data set is contained in a single table in a Microsoft Access database. The queries required for each chart are also in the database. 

How many interesting ways can you find to skin this cat?

Copyright 2001 PQ Systems.

Please direct questions or problems regarding this web site to the Webmaster.