For A Safe and Happy Holidays with Loved Ones

Ok, so about ten days ago I challenged my readers to solve the following charting problems with the Google API – Bullet Chart, Sparkline and Square Pie. I provided a sample bullet chart but asked if anyone could improve upon it. Unfortunately, only one brave soul, Jeff Donnici, took up the challenge and provided two solutions for Sparklines.

I am inviting Jeff to write his own discourse on how to create sparklines with the Google API, so Jeff, please email me (my email is at the bottom of the right-hand nav) with your description of how to create the sparklines.

First, let’s cover some basics of the API.

The API is pretty simple, it’s just a URL with a bunch of query parameters attached to define the chart and its elements. It can also be a bit annoying when you’re encoding the data sets and their associated information. My guess would be that to really get the power out of the API you would want some sort of programmatic interface to it as building the chart request ‘by hand’ can be laborious.

So, components that are used in these examples:

• Base API URL: http://chart.apis.google.com/chart?
• Required Elements
  • Chart Type Parameter: cht – Allowable Chart Types
  • Chart Size: chs – Chart Size in pixels [w]x[h] (chs=250×250)
  • Chart Data: chd – Chart data encoding methods (simple, text, extended)
• Optional Elements Used
• Chart Title: chtt – Text Title for the Chart
  • Chart Title Text Size: chts – Title font size and color
  • Bar Chart Height: chbh – Bar width
  • Chart Colors: chco - data series colors
  • Chart Background Fill: chf - Chart fill options

For me, let’s start with the Bullet Chart. The stumbling blocks here are that

1. The Google Chart API does not support combination charts, or at least not a bar chart / scatter plot combo, which is how I create Bullet Charts in Excel.
2. The API does not seem to support single data point properties. In other words, I can set the color and width properties for the stack (e.g. series) but not for data points in the stack.

If you remember the Excel example of the Bullet chart, you will recall that we have 3 data series (top, middle, bottom) each with 4 data points to create the basic visualization of the Bullet chart. We then combine into that chart a scatter plot to create the target line. Well this API doesn’t support coloring for a single data point (which is required in the Excel solution) so what does Google offer as a replacement? Well, with the background fill, we can go a linear stripe that will take up some percentage of the chart space – ok, I think I can work with that:

But first things first. Let’s define the chart type:

http://chart.apis.google.com/chart?cht=bhs

• cht=bhs tells Google that we are working with a horizontal bar chart

Next, let’s set the chart size

http://chart.apis.google.com/chart?cht=bhs&chs=250×40

• Remember, chs wants [width]x[height] in pixels and it’s required

While we’re at it, lets go ahead and set the bar width (or height if you prefer)

http://chart.apis.google.com/chart?cht=bhs&chs=250×40&chbh=10,25

• chbh lets us define that bar width and optionally the spacing between bars. In this example the gap is set to 25 to make sure that the chart fill area shows up around the bar itself.

Ok, for the last of the easy bits, let’s go ahead and set the Chart title

http://chart.apis.google.com/chart?cht=bhs&chs=250×40&chbh=10,25&chtt=Homepage+Retention+Rate:+90%

• Note the plus (+) symbols in the Chart Title text, this tells the API to use a blank space when creating the title

On to the data. As noted above, there are 3 options for encoding data – simple, text, and extended. I haven’t really figured out why you would use simple because it’s basically a substitution scheme that feels more annoying than anything. However, in this example we are using text encoding because it allows us to use regular numbers with up to 1/10th precision.

http://chart.apis.google.com/chart?cht=bhs&chs=250×40&chbh=10,25&chtt=Homepage+Retention+Rate:+90%&chd=t:90.0

• when adding the chart data, you must first tell chd how you are encoding the data (chd=t) and then supply the data itself (:90.0)

The above gives us a basic bar chart:

Now we need to add the elements that will make this look like a Bullet chart and we’re going to get it all done with chart fill!

http://chart.apis.google.com/chart?cht=bhs&chs=250×40&chbh=10,25&chtt=Homepage+Retention+Rate:+90%&chd=t:90.0&chf=c,ls,0,000000,0.50,cccccc,0.25,000000,0.005,efefef,0.245

chf (chart fill) takes the following arguments:

• fill area: background (bg) or chart area (c), in the Bullet chart, we use the chart fill method
• fill type: solid (s), linear gradient (lg), and linear stripes (ls)
• angle: angle of the stripe between 0 (horizontal) and 90 (vertical)
• stripe color and width pairs: color hex value and length expressed as a percentage

If you look at the URL snippet above in bold, you will see that we have defined

• the background element as "chart fill" (c)
• the fill type as "linear stripe" (ls)
• the "angle" as zero
• The first section is black and extends to 25% of the length
• The second fill section is the same as the first
• the third section is also 25% but is a dark gray
• the fourth section, only 1/2 of 1 percent in width is black – It’s a trick, this is the target line!
• The last section is 24 & 1/2 percent of the length and is a very light gray

That’s it, now we have a functioning bullet chart

I sure wish that I could remove the axes lines, and add data labels to the bar and target line, but oh well.

Ok, I need to take a break as this is quite a long post. In part 2, we will walk through the pie chart solution (which is not that great),

I’m not quite sure how long I’ve been ignoring the Google Chart API but I saw it in a post the other day which reminded me, or at least gave me the inkling, that I had seen some discussion of it before.

Well, that was several days ago and I finally had some time to play with it and in a couple of hours created the following bullet chart:

So here’s the challange

1. Create a Better Bullet Chart than the Above
2. Create a Sparkline
3. Create a Square Pie Chart

Using the Google Chart API of course.

Once you’ve created your solution, post a link to it here in the comments. For the solutions that we like best, I’ll ask the author to provide a description and instructions to be published here in a follow up post.

Update: 12/15/2007

Jeff Donnici sends us two sparkline examples.

Simple:

With Banded Background:

Update 12/19/2007

Well with only Jeff taking up the challenge after 10 days, here’s the final solution: a so-called square-pie chart. Of the 3 types this is the most poorly supported by the Google API mostly because on bar charts you cannot control the scale of the axes.

Update Feb 29, 2008

Geoff offers us this austere Bullet Chart solution:

And this slightly "dressed up" version:

Wow! It’s been a really, really long time since I have done one of these. According to WordPress, the last "Sharing the Wealth" post was published on September 27, 2006 – discussing how to store color assigments in VBA. Guess I haven’t been doing a lot of Excel work in the last year!

All that aside, today’s tip is just too obvious not to pass a long, although you may want to file it under ReadTheManual…

Someone asked me today "How can I match rows where the string contains the substring "abc" and total those rows?" Ok, so I’m paraphrasing but this was the question. Being a little rusty I went right along with it and said something like "Right, you can’t use SUMIF because it doesn’t do fuzzy matching."

WRONG! WRONG! WRONG!

After I got off the phone, I hopped into Excel help, not because I thought it would tell me something about SUMIF that I didn’t know but because there might be another formula to help me out or at least a kernel of an idea.

It turns out that SUMIF supports the wildcards ? and * (question mark and asterisk). Furthermore you can match on those characters be prefixing them with a ~ (tilde).

Here are some sample conditions, the appropriate wild-carding and the SUMIF formulae:

Ok, So the last two are a bit of a trick. We’re using SUM as a parent function to the two SUMIF functions to create the OR operator and the new in Excel 2007 SUMIFS function to match on multiple criteria for the AND operator.  SUMIFS is just like SUMIF but matches on multiple criteria (can be from multiple locatioins too) but notice that the SUM RANGE comes first instead of the CRITERIA RANGE which comes first in the more widely known SUMIF.

Not enough? You can download this sample spreadsheet to see it in action.

• Excel 2007 version with SUMIFS
• Excel 2003 version without SUMIFS

This is another in a long litany of my "DUH" moments with Excel, what about you? What’s your biggest DUH moment with Excel? 

Related Posts:

• Sharing the Wealth: Random Excel Tip
• Sharing the Wealth: Random Excel Tip #2
• Sharing the Wealth: Random Excel Tip #3

I use twitter more as an information source than a communication tool. That means that I am largely silent and let all my friends do the talking.
So I’m probably not fulfilling my twitter responsibilities – oh well.

Twitter has some interesting features, one of those being able to track conversations – for instance "track earthquake" will track all tweets on an earthquake. This is a great ‘personal’ meme-tracker but it would be even better if there was a public way to track memes.

It should be simple, just like how replying to tweets evolved on twitter. Twitterers started using @username to denote replies to specific people and the fine folks at twitter formalized it as funcionality so that whenever twitter sees @username the tweet goes into a ‘replies’ bucket – nice.

Now, I’m going to suggest that the fine folks at twitter institutionalize public memes. So that anyone could see the discussion on a particular topic.

Here’s what I propose, in addition to being able to do the whole ‘track’ thing for personal meme tracking. Allow ‘tagging’ of tweets via the pound/hash sign ( # ). Any tweets that are marked with #memename could then be aggregated in their own twitter page. Additionally, they could be individually trackable by track #memename.

Anyone out there who also uses twitter agree or disagree?