How do I format Google Chart data to show a count

Question

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:

[
    ['Project','PM','Year'],
    ['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 1

draw each control separately

use data.group 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([
      ['Project','PM','Year'],
      ['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
        }
      }
    };

    drawChart(data);

    var slider = new google.visualization.ControlWrapper({
      controlType: 'NumberRangeFilter',
      containerId: 'filter_div',
      dataTable: data,
      options: {
        filterColumnIndex: 2,
        ui: {
          format: {pattern: '0'}
        }
      }
    });

    google.visualization.events.addListener(slider, 'statechange', function () {
      var range = slider.getState();
      var view = new google.visualization.DataView(data);
      view.setRows(data.getFilteredRows([{
        column: 2,
        minValue: range.lowValue,
        maxValue: range.highValue
      }]));
      drawChart(view);
    });

    slider.draw();

    function drawChart(dataTable) {
      var dataGroup = google.visualization.data.group(
        dataTable,
        [1],
        [{column: 1, aggregation: google.visualization.data.count, type: 'number', label: 'Count'}]
      );

      var chart = new google.visualization.BarChart(document.getElementById('chart_div'));
      chart.draw(dataGroup, options);
    }
  },
  packages: ['controls', 'corechart']
});

<script src="https://www.gstatic.com/charts/loader.js"></script>
<div id="filter_div"></div>
<div id="chart_div"></div>