Create stacked chart using Google Charts from database

Question

My api returns the following table:

Date        Name      Size
2018-17-09  John      500
2018-17-09  Doe       1000
2018-17-09  Jack      3000
2018-17-09  Choi      700
2018-18-09  John      6000
2018-18-09  Doe       200
2018-18-09  Jack      555
2018-19-09  John      700
2018-19-09  Doe       9000

My goal is to show the chart by name and date. The x-axis will be the dates and y-axis is the size.

I am very new to google chart need some help. Thank you so much!

Hi @WhiteHat. I just need to draw the chart. I've been doing it for several days. — Jake, Sep 20 '18 at 03:31

score 0 · Accepted Answer · answered Sep 20 '18 at 11:22

in order to stack columns for each name by date,
your table needs to be structured as follows,
with columns / series for each name.

['Date', 'John', 'Doe', 'Jack'],
['2018-17-09', 500, 1000, 3000],

this can be difficult to do on the server / query,
especially if you don't know how many names you will have up front.

as such, we can use google data table methods
to transform the data from the server,
into the format we need.

first we create a data view with the necessary columns,
then we can aggregate / sum the view by date.

see following working snippet...

google.charts.load('current', {
  packages: ['corechart']
}).then(function () {
  // create data table
  var data = google.visualization.arrayToDataTable([
    ['Date', 'Name', 'Size'],
    ['2018-17-09', 'John', 500],
    ['2018-17-09', 'Doe', 1000],
    ['2018-17-09', 'Jack', 3000],
    ['2018-17-09', 'Choi', 700],
    ['2018-18-09', 'John', 6000],
    ['2018-18-09', 'Doe', 200],
    ['2018-18-09', 'Jack', 555],
    ['2018-19-09', 'John', 700],
    ['2018-19-09', 'Doe', 9000],
  ]);

  // create data view
  var view = new google.visualization.DataView(data);

  // column arrays
  var aggColumns = [];
  var viewColumns = [0];

  // build view & agg columns for each name
  data.getDistinctValues(1).forEach(function (name, index) {
    viewColumns.push({
      calc: function (dt, row) {
        if (dt.getValue(row, 1) === name) {
          return dt.getValue(row, 2);
        }
        return null;
      },
      label: name,
      type: 'number'
    });

    aggColumns.push({
      aggregation: google.visualization.data.sum,
      column: index + 1,
      label: name,
      type: 'number'
    });
  });

  // set view columns
  view.setColumns(viewColumns);

  // sum view by date
  var aggData = google.visualization.data.group(
    view,
    [0],
    aggColumns
  );

  // draw chart
  var chart = new google.visualization.ColumnChart(document.getElementById('chart_div'));
  chart.draw(aggData, {
    isStacked: true
  });
});

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

not possible using standard options, but you can [create your own legend](https://stackoverflow.com/a/36064828/5090771). — WhiteHat, Sep 21 '18 at 11:25

Create stacked chart using Google Charts from database

1 Answers1