Google charts floating min / max / average column chart

Question

I'm trying to create a Google chart that looks like the following:

http://chart.googleapis.com/chart?cht=bvs&chs=200x125&chd=t2:10,50,60,80,40%7C50,60,100,40,20%7C30,70,90,95,45&chco=4d89f900,c6d9fd&chbh=20&chds=0,160&chm=H,336699,2,-1,1:22

Basically, I just want to represent the max, min, and average all on one chart, but I can't seem to figure out how to do this. I know it's possible using markers with the old URL-based charts, but they're being deprecated and it doesn't look like the new API supports markers yet.

I tried using candlesticks, but the only way I got it working was with a skinny line and a horizontal line in the middle, so it looked like a bunch of plus signs rather than floating columns with line markers. I know I could also technically stack a column chart with a stepped area chart, but then the line is continuous across all entries, which I don't want.

Thanks.

EDIT: Using jmac's method and intervals, I came up with this:

function drawVisualization() {
  // Create and populate the data table.
  var data = new google.visualization.DataTable();
  data.addColumn('string', 'label');
  data.addColumn('number', 'filler');
  data.addColumn('number', 'range');
  data.addColumn({type:'number', role:'interval'});
  data.addRows([
    ['A', 3, 4, 2],
    ['B', 2, 5, 4],
    ['C', 4, 4, 1],
    ['D', 5, 2, 1],
    ['E', 1, 8, 4],
  ]);

  // Create and draw the visualization.
  var ac = new google.visualization.ColumnChart(document.getElementById('visualization'));
  ac.draw(data, {
    width: 600,
    isStacked: true,
    series: [{color:'transparent'},{color:'silver'},{color:'silver'}],
    vAxis: {gridlines: {color: 'transparent'}, textPosition: 'none'},
    focusTarget: 'category',
    intervals: { 'style': 'bars', 'barWidth': 1.3, 'lineWidth': 2 },
  });
}

I don't have enough reputation to post an image of what it looks like yet, but if you paste it in here you can see it: https://code.google.com/apis/ajax/playground/?type=visualization#column_chart

Also, since it still highlights the filler area when you mouse over it, I found a css hack to hide the highlighting on mouse over:

#chart-div {
    svg g g g g rect {
      stroke-width:0px;
    }
}

Answer 1

You can use "box" style intervals to accomplish what you want:

function drawChart () {
    var data = new google.visualization.DataTable();
    data.addColumn('string', 'Category');
    data.addColumn('number', 'Min');
    data.addColumn('number', 'Average');
    data.addColumn('number', 'Max');

    data.addRows([
        ['Foo', 3, 5, 7],
        ['Bar', 5, 8, 10],
        ['Baz', 0, 2, 6],
        ['Bat', 1, 2, 4]
    ]);

    var view = new google.visualization.DataView(data);
    // duplicate 1 column as a dummy data series, and add intervals to it
    view.setColumns([0, 1, {
        id: 'min',
        type: 'number',
        role: 'interval',
        calc: function (dt, row) {
            return dt.getValue(row, 1);
        }
    }, {
        id: 'avg',
        type: 'number',
        role: 'interval',
        calc: function (dt, row) {
            return dt.getValue(row, 2);
        }
    }, {
        id: 'max',
        type: 'number',
        role: 'interval',
        calc: function (dt, row) {
            return dt.getValue(row, 3);
        }
    }, 1, 2, 3]);

    var chart = new google.visualization.LineChart(document.querySelector('#chart_div'));
    chart.draw(view, {
        height: 400,
        width: 600,
        lineWidth: 0,
        intervals: {
            style: 'boxes'
        },
        legend: {
            position: 'none'
        },
        series: {
            0: {
                // dummy data series, controls color of intervals
                visibleInLegend: false,
                color: 'blue',
                enableInteractivity: false
            },
            1: {
                // min series options
            },
            2: {
                // average series options
            },
            3: {
                // max series options
            }
        }
    });
}
google.load('visualization', '1', {packages:['corechart'], callback: drawChart});

See working example: http://jsfiddle.net/asgallant/pvJpx/

Answer 2

If all you care about is how it looks visually, you can recreate this with a bit of finagling to have it look like this:

This is the code:

function drawVisualization() {
  // Create and populate the data table.
  var data = google.visualization.arrayToDataTable([
    ['label', 'filler', 'bot half', 'top half'],
    ['A', 3, 2, 2],
    ['B', 2, 4, 1],
    ['C', 4, 1, 3],
    ['D', 5, 1, 1],
    ['E', 1, 4, 4],
  ]);

  // Create and draw the visualization.
  var ac = new google.visualization.ColumnChart(document.getElementById('visualization'));
  ac.draw(data, {
    width: 600,
    isStacked: true,
    series: [{color:'transparent'},{color:'silver'},{color:'silver'}],
    vAxis: {gridlines: {color: 'transparent'}, textPosition: 'none'},
    focusTarget: 'category',
  });
}

This is a dumb workaround, but here are the steps given a min value, a max value, and an avg value:

1. Create a dummy (transparent) series equal to min
2. Create a second series for the bottom half of the bar equal to avg - min
3. Create a third series for the top half of the bar equal to max - avg

Although it looks right, the issue is that interaction with the chart will be real funky, in the sense that it won't show you what you would expect from the chart (you would have separate values that aren't showing min, max, and average, but only two values for the size of points 2) and 3) above). You can get around this with creative use of focusTarget, but that will still get you odd stuff like this:

Now you could theoretically rename your series, and use the {v:, f:} trick to make it look nicer, and that may be a good workaround, but it is very kludgy depending on your application. If you finagle it all nice and right, you would get something like this:

This is done with the following code:

function drawVisualization() {
  // Create and populate the data table.
  var data = new google.visualization.DataTable();
  data.addColumn('string', 'Series Name');
  data.addColumn('number', 'Average');
  data.addColumn('number', 'Minimum');
  data.addColumn('number', 'Maximum');
  data.addRows([
    ['A', {v:3, f:'5'}, {v:2, f:'3'}, {v:2, f:'7'}],
    ['B', {v:2, f:'6'}, {v:4, f:'2'}, {v:1, f:'7'}],
    ['C', {v:4, f:'5'}, {v:1, f:'4'}, {v:3, f:'8'}],
    ['D', {v:5, f:'6'}, {v:1, f:'5'}, {v:1, f:'8'}],
    ['E', {v:1, f:'5'}, {v:4, f:'1'}, {v:4, f:'9'}],
  ]);

  // Create and draw the visualization.
  var ac = new google.visualization.ColumnChart(document.getElementById('visualization'));
  ac.draw(data, {
    width: 600,
    isStacked: true,
    series: [{color:'transparent'},{color:'silver'},{color:'silver'}],
    vAxis: {gridlines: {color: 'transparent'}, textPosition: 'none'},
    focusTarget: 'category',
  });
}

Again, this is kludgy and not perfect (see the grey box around the filler series, that can't be helped), but it will display the info, and it can be automated using some fancy javascript and/or formatters with dataviews depending on how often the charts need to be changed and what format you get your data in.