Dealer Diagnostics
DIAGNOSTICISM: How to know your dealership in he blink of an eye

The DDR Bullet Graph Gadget

We received a lot of great feedback when we posted our step-by-step guide on building bullet graphs using the google charts api. It’s no surprise that people want to take advantage of this compact and expressive visualization, especially since ready-to-go implementations are supported by only a few high-end visualization software packages.

bullet graph google gadget

While I was getting ready to leave for Boston to attend a workshop with the inventor of bullet graphs himself, Stephen Few, it struck me the time I was about to spend offline in the air there and back could be best used taking the Google charts bullet graph to the next level–as a Google gadget designed to pull data directly from a Google spreadsheet. We’ve played around with the concept a bit since then, and had some people test it out. Here’s how you can use it for your own data.

Step 1 – Create a Google spreadsheet

If you have a Gmail or Google account you are already able to create and share Google spreadsheets. Log into your account here. If you don’t have an account yet, you’ll have to sign up. Adding data can be done through a variety of ways: type it directly to the spreadsheet, import it from a range of file formats such as Excel, add it through automatically generated forms embedded in a web page or email, remote database calls, etc.

Step 2 – Format the data for the bullet graph

Let’s declare right away that the Google spreadsheet is not Excel. On the plus side, you’re going to be able to create a bullet graph by simply highlighting your data and selecting the bullet graph gadget. You’ll also have easy ways of sharing your visualizations over the web. On the negative side, the data will need to be setup in a way that the gadget can use. Fortunately, the convention is pretty simple and once learned, you’ll be able to create twenty bullet graphs as easily as you create one.

The gadget will display a label, units, and a scale if this information is provided in the selected/highlighted range. All of these are optional.

Units — Any text within parenthesis will be used to display units in the final output, in this case we have (U.S. thousands $). Optional.

Tick marks — A number enclosed in square brackets [6] will be interpreted as the number of tick marks. It’s best if this divides evenly into the full range of your graph, but not necessary.

Label — Any other text will be used for the label: 2005 Revenue YTD in this case.

Description of required elements

In the screenshot, all of these values are in a single cell, but you can split them up into different cells in any order if you find it more convenient. The gadget reads the selected/highlighted range from left to right and once the gadget has a label, unit description, or number of ticks it will ignore any others that follow. For example, the row below will give you the exact same result.

Field order is flexible

The first numerical value in the row is always used as the featured value: 275. All other numerical values in the selected range will be interpreted as comparative values. In this case we have one comparative value: 260. Now, if you’re looking at the example and thinking, “what about column A, that has a number?” That’s actually a string, look for the first cell that has only numbers (no hypens and square brackets).

Ranges are specified as start-finish, in the example we have three ranges: 0-200, 200-250, 250-300.

You don’t have to cover all the possible values with your ranges, the gadget will insert ranges where they’re required as long as you specify the minimum and maximum values somewhere (in this case 0 and 300). You could produce the same results with these ranges 0-200, 250-300 or you can specify the total graph range and ranges you want to call out within this 0-300, 200-250. All of the below rows will give the same result.

Ranges are flexible

This is designed to make it less tedious for you to calculate the specific ranges most of interest to you.

If you want to calculate these ranges from values in your spreadsheet, format the cell using the CONCATANATE formula, like this: =CONCATANATE(H5, “-”, H6)

How to calculate ranges

Step 3 – Insert the gadget, and set your options

To insert the gadget highlight the cells we’ve assembled and click on the graph button in the toolbar, choosing gadget. Since our gadget is not yet in Google’s featured gallery (spread the word, and we’ll see what happens) you need to click on the link that says ‘Custom…’. Paste in the following url:

http://hosting.gmodules.com/ig/gadgets/file/105832571962544454761/ddr_bullet_graph_1.xml

Inserting the gadget as a custom url

You’ll be presented with a number of preferences, all of these are optional or have reasonable default values except for ‘Range’ which should already be filled in with the cell range you selected/highlighted. If you want to specify additional comparative values or ranges that aren’t represented in the cells you selected you can enter them here.

If you want to change the colors of your comparative values you can enter a color using html hex values. A bright red would be specified with ff0000, a more subdued red that doesn’t steal as much attention from the viewers eye would be 990000. Try hitting apply and you should see your first gadget.

Most of the time you should stick to black for most aspects of the graph, if you need help getting colors in 6 character hex format there are plenty of color scheme selectors available on the web, one of my favorite sites for choosing colors that look good in data visualizations is ColorBrewer.

The gadget will automatically select a lower saturation for each additional range and comparative value you have according to Few’s bullet graph specification. The left range is always the darkest in the current implementation and it will be between 35% and 50% of the saturation of the color you specify.

Another important preference is CSS (cascading style sheets). All the elements produced in the graph are classed as ddr_bullet_graph_gadget, and the table cells are also classed as ddr_bullet_graph_column1 and ddr_bullet_graph_column2. If you plan to post the bullet graphs on your website and want to modify the padding, margins, or font-family to better match your site design you can turn off the default css and use your own styles by unchecking the box.

Finally, you also have the option of setting the size of the bullet graph in pixels, and putting the comparative measure bars above or behind the feature bar.

Step 4 – Create a stack of bullet graphs

One bullet graph on its own is fine, but graphical representations of data are made more useful when placed in the same visual field as other relevant information. You already know everything you need to make this happen, just select more than one row of data to place it in a bullet graph. While I was developing this code the Olypmpics were on so I kept a simple spreadsheet with Olympic medal counts.

I started with a few sources of data. The 2008 medal counts, are my feature measure, the 2004 medal counts represent a comparative value. I also wanted to see how each country was doing against projections of 2008 performance. PriceWaterhouseCoopers publishes a research paper projecting medal counts, and Andrew Bernard of Dartmouth does the same. I used this data to compute a projected range column which will put a range in the bullet graph indicating if the country managed to achieve a result consistent with what the experts expected of them.

The total range and ticks columns are computed using formula that find a range that will include all the values in the spreadsheet and then makes that range divisible by 20. I did it like this so the graph range would dynamically update if the 2008 medal data required it. Here are the formulas I used to create these calculated values.


=CONCATENATE(0,"-",(CEILING(MAX(B2:B8,C2:C8,G2:G8,H2:H8)+1,20)))
=CONCATENATE("[",((CEILING(MAX(B2:B8,C2:C8,G2:G8,H2:H8)+1,20)))/20,"]")

Putting together the gadget

Step 5 – Spread the insights

You’re free to use the Dealer Diagnostics bullet graph gadget anywhere and anyhow you want, it’s under the MIT open source license. If you think of a way to make it better, feel more than free to tell us about it or submit a patch. We have a few more enhancements planned based on some of the feedback from our beta testers, including an option to have a dot instead of a bar, vertical bullet graphs, and some extra display options.

Google spreadsheets can be easily shared with others (do we need to tell you to check with your company’s security policy before posting your data on the web?) and it’s possible to take this one step further. Click on the small arrow in the top right of your gadget and you’ll see options to publish on the web, or add to iGoogle.

Publishing your gadget

Sharing it on the web gives you a code snippet which you can use to embed your gadget directly into a website. It’s a fast way to visualize data quickly for a blog post. When the page is reloaded it pulls updated (i.e. most recent) data down from your spreadsheet.

2008 Olympics medal count by country

Vertical line is 2004 result, medium grey range is projected result.
sources: 1 2 3 4

The tight range on the Great Britain graph showed that both of the projections we used were in close agreement on what to expect, a performance very close to 2004. While China was the talk of the games, the graphs suggest that the UK was the real story when it came to beating expectations.

If you’re wishing that the Google logo wasn’t there, subscribe to our rss feed. In a future post I’ll show advanced users how to use the Google visualization API without the gadget container and even without having to use data from Google spreadsheets.

Bonus Step – A simple personal dashboard

Adding the gadget to iGoogle places the it on your custom iGoogle home page where you can organize it in columns and tabs. You also get access to a new preference which allows you to set a refresh rate for the graphs without requiring a page reload, minute-by-minute if your data is being updated that rapidly. Click here to try it out.

Like us, some of you have probably never played with iGoogle before, but you might think of it as a personal dashboard where you monitor a set of indicators: usually things like recent mail, weather, important headlines, and stock performance. There are hundreds of gadgets built by Google or various third parties and you can access a lot of decent data visualization gadgets for making common types of graphs from within Google spreadsheets. You have to make the data you share with gadgets on iGoogle publicly accessible so this wouldn’t work for businesses viewing sensitive data. But you might use this for prototyping web dashboards, or for displaying non-sensitive data (your fantasy baseball stats from a shared spreadsheet), or publicly available data that you can freely republish (economic statistics, global financial indicators, interest rates, inflation, real estate data).

You’re not going to make the most advanced dashboard with these tools, this is pretty quick and dirty stuff. However, by loading your data into Google spreadsheets, using Google gadgets for visualization (especially now that you can use bullet graphs instead of gauges), and organizing them on iGoogle, you can rapidly create a simple but effective web-based dashboard. I bet a lot of you could use these tools to out-design many of the examples posted on the websites of big-name business intelligence software… in an afternoon.

Let’s see your chops, show and tell us about your results in the comments.

  1. 13 Responses to “The DDR Bullet Graph Gadget”

  2. Really awesome!

    By Edial on Sep 9, 2008

  3. Great gadget! I started just playing around with it but now I’m using it to create graphs for my home buisness! Thanks!

    By web on Sep 18, 2008

  4. I’m trying to use this gadget and it’s not working…should it still work? I don’t know enough about how to debug it, but it just sits there and never produces a graph, even though i’m using the exact data from the examples..

    thx
    a

    By andrew on Dec 7, 2008

  5. btw, i notice in your example spreadsheet you are using http://dealerdiagnostics.com/ddr_bullet_graph_gcharts_beta2a.xml

    can you make sure this is the same code you have checked in to google hosting?

    thx
    a

    By andrew on Dec 7, 2008

  6. Andrew,

    It should be fixed now. It looks like google made a change in their analytics library API (not cool, google) and I had to make some adjustments to account for it. The sample spreadsheet url didn’t have analytics because it was development code which is why it was working ok.

    I did notice that on Firefox the gadget is resizing incorrectly on spreadsheets (works fine in Safari) and cutting off some information, though it resizes correctly on iGoogle. I will see if I can resolve this issue as well, as it seems to be a new one.

    Thanks for the heads up, we’d love to hear about how you’re using the gadget.

    By kalin on Dec 7, 2008

  7. This is really great!
    I’m working on apply this to my analysis.
    But I’m wondering could we also manually set up the range colour instead of one colour with different tone?
    Also, could we change the label of range? like show as percentage instead of “real number”?

    By Emily on Apr 7, 2009

  8. Emily,

    You could probably edit the source code of the gadget pretty easily to handle different colors, but do so cautiously. I recommend reading Stephen Fews books for a good discussion about how to use color judiciously.

    As for doing percentages, I recommend just putting the symbol in one place as (%) or (as a %) and then just use a range of 0-100. You get a much cleaner display if you don’t repeat the % symbol with every number.

    Good luck, I’d love to see what you do with the gadget.

    By kalin on Apr 7, 2009

  9. Thank you much for sharing this gadget! I wanted to ask if the white space could be reduced so as to fit the bullet graph in-cell in my Google spreadsheet. Thanks!

    By Sonali on Jan 5, 2010

  10. @Sonali thanks for the feedback

    I haven’t played with this gadget for a while but I don’t believe that google spreadsheets allows proper ‘in cell’ gadgets (they definitely didn’t when we released this, but if that’s changed do let me know). I’m assuming you mean to get an effect similar to Bonavista Microcharts http://www.bonavistasystems.com/Products_SparkLiner_ExcelUser.html

    Google’s gadgets are geared towards visualizing the data on the web, where you can do a lot to reduce the whitespace using custom css. Google allows you to edit css for text documents, but there is no simple way of applying custom css to a spreadsheet document. It would probably be possible to add a field to the gadget to allow for custom css to be added in the settings (probably not a bad idea actually), but I don’t know if that would solve your problem. Let me know if this would help, or feel free to submit a patch to do this and I will gladly add the feature.

    By kalin on Jan 9, 2010

  11. Does anyone know how to get negative values working with this? I’ve tried, but with no success

    By andrew on Apr 27, 2012

  1. 3 Trackback(s)

  2. Sep 15, 2008: DEALER DIAGNOSTICS » Blog Archive » Create Bullet Graphs with Google Charts in 7 Easy Steps
  3. Oct 7, 2008: Dashboard Zone » HR Management Dashboard
  4. Jan 8, 2009: Visual Business Intelligence - Software Support for Bullet Graphs—An Increasingly Popular Means of Display

Post a Comment

©2008 Dealer Diagnostics Inc. All rights reserved | Entries (rss) | Comments (rss)