aggregating data with filters in dashboard in google charts

Question

//Google charts with filtered data

     google.charts.load('visualization', '1', {packages: ['controls']});
      google.charts.setOnLoadCallback(drawDashboard);

      function drawDashboard() {

        // Create our data table.
        var data = new google.visualization.DataTable();
      data.addColumn('string', 'Category');
      data.addColumn('string', 'Month');
      data.addColumn('number', 'coverage');
      data.addColumn('number', 'coverage_change');
      data.addColumn('number', 'depth');
      data.addColumn('number', 'depth_change');
      data.addColumn('number', 'breadth');
      data.addColumn('number', 'breadth_change');
      data.addRows([
        ['Restaurants','Jan',0.177,0,2.489329,0,112.019805,0],
        ['Hotels','Jan',0.3411,0,1.216445,0,52.307135,0],
        ['Movies','Jan',0.4748,0,0.312464,0,9.686384,0],
        ['Attractions','Jan',0,0,0,0,0,0],
        ['Destinations','Jan',0,0,0,0,0,0],
        ['Events','Jan',0,0,0,0,0,0],
        ['All','Jan',0.2092,0,4.018238,0,174.013324,0],
        ['Restaurants','Feb',0.177,0,2.489329,0,112.019805,0],
        ['Hotels','Feb',0.3411,0,1.216445,0,52.307135,0],
        ['Movies','Feb',0.4748,0,0.312464,0,9.686384,0],
        ['Attractions','Feb',0,0,0,0,0,0],
        ['Destinations','Feb',0,0,0,0,0,0],
        ['Events','Feb',0,0,0,0,0,0],
        ['All','Feb',0.2092,0,4.018238,0,174.013324,0]


      ]);



        // Create a dashboard.
        var dashboard = new      google.visualization.Dashboard(document.getElementById('dashboard_div'));

        // Create a range slider, passing some options
        var filter = new google.visualization.ControlWrapper({
          'controlType': 'CategoryFilter',
          'containerId': 'filter_div',
          'options': {
            'filterColumnLabel': 'Category'},
            'ui': {
              'allowTyping': false,
              'allowMultiple': false,
              'labelStacking': 'vertical'
            }
        });

        // Create a pie chart, passing some options
        var Chart = new google.visualization.ChartWrapper({
          'chartType': 'LineChart',
          'containerId': 'chart1_div',
          'options': {
            'width': 1000,
            'height': 300},
            'view': {'columns': [1,2]},
            'dataTable' : google.visualization.data.group(data, [0],
                [{'column': 2, 'aggregation': google.visualization.data.sum, 'type': 'number'}])
          });


         var Chart2 = new google.visualization.ChartWrapper({
          'chartType': 'LineChart',
          'containerId': 'chart2_div',
          'options': {
            'width': 1000,
            'height': 300,
            curveType:'function'},
            'view': {'columns': [1,3]} 
        });

        // Establish dependencies, declaring that 'filter' drives 'pieChart',
        // so that the pie chart will only display entries that are let through
        // given the chosen slider range.

         dashboard.bind(filter, [Chart2,Chart]);
       // Draw the dashboard.
        dashboard.draw(data);
      }
  </script>

//I was able to get two charts with the same filter. But the values are not //aggregated. I used google.visualization.data.group function.. but, it doesn't //seem to be working.

Answer 1

only one DataTable may be used per Dashboard

so when the Dashboard is drawn, the DataTable on Chart is ignored

because it is included in the Dashboard

---

in order to draw a chart using the aggregated and filtered data from the Dashboard,

draw the Chart independently using the DataTable from Chart2,
when the 'ready' event fires

this event will fire every time Chart2 is redrawn as a result of the filter

---

see following working snippet...

google.charts.load('current', {
  callback: drawDashboard,
  packages:['controls']
});

function drawDashboard() {
  var data = new google.visualization.DataTable();
  data.addColumn('string', 'Category');
  data.addColumn('string', 'Month');
  data.addColumn('number', 'coverage');
  data.addColumn('number', 'coverage_change');
  data.addColumn('number', 'depth');
  data.addColumn('number', 'depth_change');
  data.addColumn('number', 'breadth');
  data.addColumn('number', 'breadth_change');
  data.addRows([
    ['Restaurants','Jan',0.177,0,2.489329,0,112.019805,0],
    ['Hotels','Jan',0.3411,0,1.216445,0,52.307135,0],
    ['Movies','Jan',0.4748,0,0.312464,0,9.686384,0],
    ['Attractions','Jan',0,0,0,0,0,0],
    ['Destinations','Jan',0,0,0,0,0,0],
    ['Events','Jan',0,0,0,0,0,0],
    ['All','Jan',0.2092,0,4.018238,0,174.013324,0],
    ['Restaurants','Feb',0.177,0,2.489329,0,112.019805,0],
    ['Hotels','Feb',0.3411,0,1.216445,0,52.307135,0],
    ['Movies','Feb',0.4748,0,0.312464,0,9.686384,0],
    ['Attractions','Feb',0,0,0,0,0,0],
    ['Destinations','Feb',0,0,0,0,0,0],
    ['Events','Feb',0,0,0,0,0,0],
    ['All','Feb',0.2092,0,4.018238,0,174.013324,0]
  ]);

  var dashboard = new google.visualization.Dashboard(document.getElementById('dashboard_div'));

  var filter = new google.visualization.ControlWrapper({
    'controlType': 'CategoryFilter',
    'containerId': 'filter_div',
    'options': {
      'filterColumnLabel': 'Category'},
      'ui': {
        'allowTyping': false,
        'allowMultiple': false,
        'labelStacking': 'vertical'
      }
  });

  var Chart2 = new google.visualization.ChartWrapper({
    'chartType': 'LineChart',
    'containerId': 'chart2_div',
    'options': {
      'width': 1000,
      'height': 300,
      curveType:'function'},
      'view': {'columns': [1,3]}
  });

  google.visualization.events.addListener(Chart2, 'ready', function () {
    var Chart = new google.visualization.ChartWrapper({
      'chartType': 'LineChart',
      'containerId': 'chart1_div',
      'options': {
        'width': 1000,
        'height': 300
      },
      'dataTable' : google.visualization.data.group(
        Chart2.getDataTable(),
        [0],
        [{'column': 2, 'aggregation': google.visualization.data.sum, 'type': 'number'}]
      )
    });
    Chart.draw();
  });

  dashboard.bind(filter, [Chart2]);
  dashboard.draw(data);
}

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