CODEWITHSUNDEEP
chartsgoogle-visualization
Sach
Sach

Reputation: 171

Google chart: different color for each record of a particular column based on its value

I just learnt of google charts in https://carlos-sierra.net/2014/07/28/free-script-to-generate-a-line-chart-on-html/.

I have one question. Lets say i have below kind of data.

Also posting a photo of it below:

TIME – containing the sample time from ash truncated to MM/DD/YYYY-HH24:MI
W – containing the wait event, if none, then CPU.
SMPL – count of samples for that event in that “TIME” minute


TIME W SMPL
4/13/2017-19:44 CPU 1
4/13/2017-19:46 log file sync 1
4/13/2017-19:46 CPU 1
4/13/2017-19:46 CPU 1
4/13/2017-19:47 db file sequential read 1
4/13/2017-19:47 db file sequential read 1
4/13/2017-19:47 log file sync 1
4/13/2017-19:47 CPU 1
4/13/2017-19:47 CPU 1
4/13/2017-19:48 CPU 1
4/13/2017-19:49 CPU 1
4/13/2017-19:50 db file sequential read 1
4/13/2017-19:50 flashback log file sync 1
4/13/2017-19:50 log file sync 4
4/13/2017-19:50 control file sequential read 1
4/13/2017-19:50 log file parallel write 1
4/13/2017-19:50 flashback log file write 1
4/13/2017-19:51 db file sequential read 1
4/13/2017-19:52 CPU 1
4/13/2017-19:52 null event 1

enter image description here

i want to make a stacked area chart using google charts only.

My requirement is : w should be a variable where the value of the event will result in a different color. I dont want to make a different column for each wait separately. How can i achieve that?

I am looking for a kind of graph (photo given below) which i can get if i copy the above data in an excel and make a pivot chart:

AXIS(categories)-TIME
LEGEND(series)-W
VALUES-Sum of SMPL

like given belowenter image description here

enter image description here

I dont know how to code it exactly using data.addColumn or google.visualization.arrayToDataTable functions. i tried but no luck yet.

Upvotes: 1

Views: 204

Answers (1)

WhiteHat
WhiteHat

Reputation: 61232

you'll need to separate the values for w into separate columns using a DataView
and the setColumns() method

then the view will need to be aggregated for each timestamp
using the group() method

see following working snippet... 

google.charts.load('current', {
  callback: function () {
    drawChart();
    window.addEventListener('resize', drawChart, false);
  },
  packages:['corechart', 'table']
});

function drawChart() {
  var data = google.visualization.arrayToDataTable([
    ['TIME', 'W', 'SMPL'],
    ['4/13/2017 19:44', 'CPU', 1],
    ['4/13/2017 19:46', 'log file sync', 1],
    ['4/13/2017 19:46', 'CPU', 1],
    ['4/13/2017 19:46', 'CPU', 1],
    ['4/13/2017 19:47', 'db file sequential read', 1],
    ['4/13/2017 19:47', 'db file sequential read', 1],
    ['4/13/2017 19:47', 'log file sync', 1],
    ['4/13/2017 19:47', 'CPU', 1],
    ['4/13/2017 19:47', 'CPU', 1],
    ['4/13/2017 19:48', 'CPU', 1],
    ['4/13/2017 19:49', 'CPU', 1],
    ['4/13/2017 19:50', 'db file sequential read', 1],
    ['4/13/2017 19:50', 'flashback log file sync', 1],
    ['4/13/2017 19:50', 'log file sync', 4],
    ['4/13/2017 19:50', 'control file sequential read', 1],
    ['4/13/2017 19:50', 'log file parallel write', 1],
    ['4/13/2017 19:50', 'flashback log file write', 1],
    ['4/13/2017 19:51', 'db file sequential read', 1],
    ['4/13/2017 19:52', 'CPU', 1],
    ['4/13/2017 19:52', 'null event', 1]
  ]);

  var colorPallette = ['#273746','#707B7C','#dc7633','#f1c40f','#1e8449','#2874a6','#6c3483','#922b21'];
  var wValues = data.getDistinctValues(1);
  var viewColumns = [0];
  var aggColumns = [];
  wValues.forEach(function (w, index) {
    viewColumns.push({
      calc: function (dt, row) {
        var wValue = null;
        if (dt.getValue(row, 1) === w) {
          wValue = dt.getValue(row, 2);
        }
        return wValue;
      },
      label: w,
      type: 'number'
    });

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

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

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

  var container = document.getElementById('chart_div');
  var chart = new google.visualization.AreaChart(container);
  chart.draw(groupView, {
    areaOpacity: 1,
    colors: colorPallette,
    isStacked: true
  });
  var table = new google.visualization.Table(document.getElementById('table_div'));
  table.draw(groupView);
}
<script src="https://www.gstatic.com/charts/loader.js"></script>
<div id="chart_div"></div>
<div id="table_div"></div>

Upvotes: 1

Related Questions