Summarize Categories By Year In Google Column Chart

I have a data table with number of car sales in the last 3 years. I want to create a column chart that has columns for 'leased', 'financed', and 'cash sale' in each year. My table

Solution 1:

first, build a DataView with columns for each sale type

then use the group method to aggregate the view

see following working snippet...

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

functiondrawChart() {
  var data = google.visualization.arrayToDataTable([
    ['Sale Date', 'Sale Type'],
    [newDate(2016, 0, 16), 'cash sale'],
    [newDate(2016, 0, 16), 'cash sale'],
    [newDate(2016, 0, 16), 'leased'],
    [newDate(2016, 0, 16), 'leased'],
    [newDate(2016, 0, 16), 'financed'],
    [newDate(2017, 0, 16), 'cash sale'],
    [newDate(2017, 0, 16), 'cash sale'],
    [newDate(2017, 0, 16), 'cash sale'],
    [newDate(2017, 0, 16), 'financed'],
    [newDate(2016, 0, 17), 'cash sale'],
    [newDate(2016, 0, 17), 'financed'],
    [newDate(2016, 0, 17), 'cash sale'],
    [newDate(2016, 0, 17), 'leased'],
    [newDate(2016, 0, 17), 'financed'],
    [newDate(2017, 0, 17), 'financed'],
    [newDate(2017, 0, 17), 'financed'],
    [newDate(2017, 0, 17), 'cash sale'],
    [newDate(2017, 0, 17), 'financed'],
    [newDate(2016, 0, 18), 'leased'],
    [newDate(2016, 0, 18), 'cash sale'],
    [newDate(2017, 0, 18), 'cash sale'],
    [newDate(2017, 0, 18), 'cash sale']
  ]);

  // build view and aggregation columnsvar viewColumns = [{
    label: 'year',
    type: 'string',
    calc: function (dt, row) {
      return dt.getValue(row, 0).getFullYear().toString();
    }
  }];
  var aggColumns = [];
  var saleTypes = data.getDistinctValues(1);
  saleTypes.forEach(function (saleType) {
    var colIndex = viewColumns.push({
      label: saleType,
      type: 'number',
      calc: function (dt, row) {
        return (dt.getValue(row, 1) === saleType) ? 1 : 0;
      }
    });
    aggColumns.push({
      aggregation: google.visualization.data.sum,
      column: colIndex - 1,
      label: saleType,
      type: 'number'
    });
  });

  var view = new google.visualization.DataView(data);
  view.setColumns(viewColumns);

  var summary = google.visualization.data.group(
    view,
    [0],
    aggColumns
  );

  var container = document.body.appendChild(document.createElement('div'));
  var chart = new google.visualization.ColumnChart(container);
  chart.draw(summary, {
    legend: {
      position: 'top'
    }
  });
}

<scriptsrc="https://www.gstatic.com/charts/loader.js"></script>