ChrisPEditor ChrisPEditor - 1 year ago 104
JSON Question

How do I format Google Chart data to show a count of items, but be filterable by year

Hopefully this is appropriate for stackexchange. If not, my apologies.

Anyway, I have a set of data that is essentially a list of projects, the person tied to each project, and a year in which the project was started. What I'd like to do is render a bar chart that shows a total count of projects per individual, but somehow maintain the year in which an individual project was started so I can create a slider filter.

That sounds confusing, writing it out. The data looks like this:

['PRJ0001','John Doe','2012'],
['PRJ0002','John Doe','2012'],
['PRJ0003','Jackie Johnson','2013'],
Etc, etc,

I have no problem creating a count by project manager name, but I "lose" the year as a metric on which I can filter. Anyone have any ideas?

Answer Source

draw each control separately

use to get counts per person and draw chart

draw the slider with the original data (need to change column type for NumberRangeFilter)

redraw the chart on slider 'statechange'
using slider low / high values to build a view using getFilteredRows

see following working snippet...

google.charts.load('current', {
  callback: function () {
    var data = google.visualization.arrayToDataTable([
      ['PRJ0001','John Doe',2012],
      ['PRJ0002','John Doe',2012],
      ['PRJ0003','John Doe',2012],
      ['PRJ0004','John Doe',2013],
      ['PRJ0005','Jackie Johnson',2012],
      ['PRJ0006','Jackie Johnson',2013],
      ['PRJ0007','Jackie Johnson',2014]

    var options = {
      height: 400,
      hAxis: {
        viewWindow: {
          min: 0,
          max: 5


    var slider = new google.visualization.ControlWrapper({
      controlType: 'NumberRangeFilter',
      containerId: 'filter_div',
      dataTable: data,
      options: {
        filterColumnIndex: 2,
        ui: {
          format: {pattern: '0'}
    });, 'statechange', function () {
      var range = slider.getState();
      var view = new google.visualization.DataView(data);
        column: 2,
        minValue: range.lowValue,
        maxValue: range.highValue


    function drawChart(dataTable) {
      var dataGroup =
        [{column: 1, aggregation:, type: 'number', label: 'Count'}]

      var chart = new google.visualization.BarChart(document.getElementById('chart_div'));
      chart.draw(dataGroup, options);
  packages: ['controls', 'corechart']
<script src=""></script>
<div id="filter_div"></div>
<div id="chart_div"></div>