CODEWITHSUNDEEP
javascriptjquerychartsgoogle-visualization
Pirate X
Pirate X

Reputation: 3103

Google Column Charts with Groups

My data format -

['Group','Count','Month','Year'],
['A',10,'February',2015],
['B',8,'February',2015],
['C',15,'February',2016]

I will be using a filter to display data for each month separated by Group column.

X-axis will have Groups. Y-Axis will have Counts for both all years (2014, 2015, 2016...).

Something like this enter image description here

I am using Google Dashboard for this. My code-

var dashboard = new google.visualization.Dashboard(document.getElementById('dashboard4_div'));
var slider = new google.visualization.ControlWrapper({
          'controlType': 'CategoryFilter',
          'containerId': 'filter4_div',
          'options': {
            'filterColumnLabel': 'Month',
            'ui': {
            'allowTyping': true,
            'allowMultiple': false,
            'allowNone': false,
            'sortValues': false,
            'label': 'Choose month',
        }
          }
        });

var ColumnChart = new google.visualization.ChartWrapper({
    'chartType': 'ColumnChart',
    'containerId': 'chart4_div',

// How to take in two column values 

});
dashboard.bind(slider, ColumnChart);

        // Draw the dashboard.
        dashboard.draw(data);

I want to know how to add two column values in my chart using Google Charts

Upvotes: 2

Views: 2167

Answers (1)

WhiteHat
WhiteHat

Reputation: 61275

since the data requires manipulation before drawing the chart,
draw the Category Filter and Chart independently

use the original data table for slider
then when either the 'ready' or 'statechange' events occur on slider,
use selectedValues to filter the rows

once filtered, use data.group to transform years from rows to columns

see following working snippet...

google.charts.load('current', {
  callback: function () {
    var data = google.visualization.arrayToDataTable([
      ['Group', 'Count', 'Month', 'Year'],
      ['A', 10, 'February', 2015],
      ['B', 8, 'February', 2015],
      ['C', 15, 'February', 2016],
      ['A', 7, 'February', 2016],
      ['B', 5, 'February', 2016],
      ['C', 12, 'February', 2015],
      ['A', 20, 'March', 2015],
      ['B', 16, 'March', 2015],
      ['C', 30, 'March', 2016],
      ['A', 14, 'March', 2016],
      ['B', 10, 'March', 2016],
      ['C', 24, 'March', 2015]
    ]);

    var slider = new google.visualization.ControlWrapper({
      'controlType': 'CategoryFilter',
      'containerId': 'filter_div',
      'dataTable': data,
      'options': {
        'filterColumnLabel': 'Month',
        'ui': {
          'allowTyping': true,
          'allowMultiple': false,
          'allowNone': false,
          'sortValues': false,
          'label': 'Choose month',
        }
      }
    });

    google.visualization.events.addListener(slider, 'ready', drawChart);
    google.visualization.events.addListener(slider, 'statechange', drawChart);

    function drawChart() {
      var sliderData = new google.visualization.DataView(slider.getDataTable());
      sliderData.setRows(sliderData.getFilteredRows([{
        column: 2,
        value: slider.getState().selectedValues[0]
      }]));

      // group by 'Group' / 'Year'
      var dataGroup = google.visualization.data.group(
        sliderData,
        [0, 3],
        [{column: 1, aggregation: google.visualization.data.sum, type: 'number', label: 'Count'}]
      );
      dataGroup.sort([{column: 0},{column: 1}]);

      // build final data table
      var yearData = new google.visualization.DataTable({
        cols: [
          {label: 'Group', type: 'string'}
        ]
      });

      // add column for each year
      var years = dataGroup.getDistinctValues(1);
      for (var i = 0; i < years.length; i++) {
        yearData.addColumn(
          {label: years[i], type: 'number'}
        );
      }

      // add row for each month
      var rowMonth = null;
      var rowIndex = null;
      for (var i = 0; i < dataGroup.getNumberOfRows(); i++) {
        if (rowMonth !== dataGroup.getValue(i, 0)) {
          rowMonth = dataGroup.getValue(i, 0);
          rowIndex = yearData.addRow();
          yearData.setValue(rowIndex, 0, rowMonth);
        }
        for (var x = 1; x < yearData.getNumberOfColumns(); x++) {
          if (yearData.getColumnLabel(x) === dataGroup.getValue(i, 1).toString()) {
            yearData.setValue(rowIndex, x, dataGroup.getValue(i, 2));
          }
        }
      }

      var view = new google.visualization.DataView(yearData);
      view.setColumns([0, 1, {
          calc: 'stringify',
          sourceColumn: 1,
          type: 'string',
          role: 'annotation'
        }, 2, {
          calc: 'stringify',
          sourceColumn: 2,
          type: 'string',
          role: 'annotation'
      }]);

      var ColumnChart = new google.visualization.ChartWrapper({
        'chartType': 'ColumnChart',
        'containerId': 'chart_div',
        'dataTable': view
      });
      ColumnChart.draw();
    }

    slider.draw();
  },
  packages: ['controls', 'corechart']
});
<script src="https://www.gstatic.com/charts/loader.js"></script>
<div id="dashboard_div">
  <div id="filter_div"></div>
  <div id="chart_div"></div>
</div>

EDIT

updated for multiple filters...

add duplicate code for additional filter.
draw second filter when first is ready.
draw chart when second filter is ready.
add all filter values to getFilteredRows statement.

see following working snippet...

google.charts.load('current', {
  packages: ['controls', 'corechart']
}).then(function () {
  var data = google.visualization.arrayToDataTable([
    ['Group', 'Count', 'Month', 'Year'],
    ['A', 10, 'February', 2015],
    ['B', 8, 'February', 2015],
    ['C', 15, 'February', 2016],
    ['A', 7, 'February', 2016],
    ['B', 5, 'February', 2016],
    ['C', 12, 'February', 2015],
    ['A', 20, 'March', 2015],
    ['B', 16, 'March', 2015],
    ['C', 30, 'March', 2016],
    ['A', 14, 'March', 2016],
    ['B', 10, 'March', 2016],
    ['C', 24, 'March', 2015]
  ]);

  var filter = new google.visualization.ControlWrapper({
    controlType: 'CategoryFilter',
    containerId: 'filter_div_group',
    dataTable: data,
    options: {
      filterColumnLabel: 'Group',
      ui: {
        allowTyping: true,
        allowMultiple: false,
        allowNone: false,
        sortValues: false,
        label: 'Choose group',
      }
    }
  });

  var slider = new google.visualization.ControlWrapper({
    controlType: 'CategoryFilter',
    containerId: 'filter_div_month',
    dataTable: data,
    options: {
      filterColumnLabel: 'Month',
      ui: {
        allowTyping: true,
        allowMultiple: false,
        allowNone: false,
        sortValues: false,
        label: 'Choose month',
      }
    }
  });

  google.visualization.events.addListener(slider, 'ready', function () {
    filter.draw();
  });
  google.visualization.events.addListener(slider, 'statechange', drawChart);
  google.visualization.events.addListener(filter, 'ready', drawChart);
  google.visualization.events.addListener(filter, 'statechange', drawChart);

  function drawChart() {
    // add both filter values to getFilteredRows
    var sliderData = new google.visualization.DataView(slider.getDataTable());
    sliderData.setRows(sliderData.getFilteredRows([{
      column: 0,
      value: filter.getState().selectedValues[0]
    }, {
      column: 2,
      value: slider.getState().selectedValues[0]
    }]));

    // group by 'Group' / 'Year'
    var dataGroup = google.visualization.data.group(
      sliderData,
      [0, 3],
      [{column: 1, aggregation: google.visualization.data.sum, type: 'number', label: 'Count'}]
    );
    dataGroup.sort([{column: 0},{column: 1}]);

    // build final data table
    var yearData = new google.visualization.DataTable({
      cols: [
        {label: 'Group', type: 'string'}
      ]
    });

    // add column for each year
    var years = dataGroup.getDistinctValues(1);
    for (var i = 0; i < years.length; i++) {
      yearData.addColumn(
        {label: years[i], type: 'number'}
      );
    }

    // add row for each month
    var rowMonth = null;
    var rowIndex = null;
    for (var i = 0; i < dataGroup.getNumberOfRows(); i++) {
      if (rowMonth !== dataGroup.getValue(i, 0)) {
        rowMonth = dataGroup.getValue(i, 0);
        rowIndex = yearData.addRow();
        yearData.setValue(rowIndex, 0, rowMonth);
      }
      for (var x = 1; x < yearData.getNumberOfColumns(); x++) {
        if (yearData.getColumnLabel(x) === dataGroup.getValue(i, 1).toString()) {
          yearData.setValue(rowIndex, x, dataGroup.getValue(i, 2));
        }
      }
    }

    var view = new google.visualization.DataView(yearData);
    view.setColumns([0, 1, {
        calc: 'stringify',
        sourceColumn: 1,
        type: 'string',
        role: 'annotation'
      }, 2, {
        calc: 'stringify',
        sourceColumn: 2,
        type: 'string',
        role: 'annotation'
    }]);

    var ColumnChart = new google.visualization.ChartWrapper({
      'chartType': 'ColumnChart',
      'containerId': 'chart_div',
      'dataTable': view
    });
    ColumnChart.draw();
  }

  slider.draw();
});
<script src="https://www.gstatic.com/charts/loader.js"></script>
<div id="dashboard_div">
  <div id="filter_div_group"></div>
  <div id="filter_div_month"></div>
  <div id="chart_div"></div>
</div>

Upvotes: 1

Related Questions