Google Column Chart with two columns

Question

How to approach a column chart when having month and year.

My data is in this format

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

Aim is to create a column chart which has X-axis as Month and Y-axis as Count. Now X-axis should have Count for years grouped by month. Something like this -

I tried to simply pass the above data to see what I can get, but I get error.

Any way to assign Axis the values based on year grouped by month ?

JsFiddle of Google Chart Example

Answer 1

just need three columns, something like this...

  ['Month', '2015', '2016'],
  ['Jan', 10, 15],
  ['Feb', 12, 18],
  ['Mar', 14, 21],
  ['Apr', 16, 24]

then you can use a DataView to add the annotations, via calculated columns...

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

see following working snippet...

google.charts.load('current', {
  callback: function () {
    var data = google.visualization.arrayToDataTable([
      ['Month', '2015', '2016'],
      ['Jan', 10, 15],
      ['Feb', 12, 18],
      ['Mar', 14, 21],
      ['Apr', 16, 24]
    ]);

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

    var chart = new google.visualization.ColumnChart(document.getElementById('chart_div'));
    chart.draw(view, {});
  },
  packages: ['corechart']
});

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

EDIT

all google charts require a specific DataFormat

meaning, manipulation is required if your data does not already exist in this format

the visualization library does offer some Data Manipulation Methods, such as group()

which could be used to transform the data into the required format

1) group the data by Month and Year
2) create a new DataTable with the Month column
3) add a column for each Year from the grouped table
4) add the rows for each month

see following working snippet, using the data from the question...

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]
    ]);

    // group by month / year
    var dataGroup = google.visualization.data.group(
      data,
      [2, 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: 'Month', 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 container = document.getElementById('chart_div');
    var chart = new google.visualization.ColumnChart(container);
    chart.draw(view);
  },
  packages: ['corechart']
});

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