Search code examples
chartsgoogle-visualizationlegend-propertiescolumn-chart

Google Bar Chart - custom Legend text with total values & column wise total for Table chart

i would like get the total value of Grades in Stacked Bar Graph Legend. picture attached how i wud like to have... for the following working snippet.

and also the Column wise Total for the Table Chart...

i think this can be done with following but how to get it done..

data.setFormattedValue(i, 0, key + ' (' + val + ')');

Current look

Expected result

can have something like this.. when mouseover one location on Bar... need to show all the grades values in single annotation..??

Mouseover On Bar for the location

is it possible to have the different values in highlighted places like in below picture? circle B is overall total of Grade1 in graph is correct. and circle A the tooltip total should show for that particular locations total of every Grade's annotation like below.

['Location', 'Company', 'Grade1',{ role: 'annotation' }, 'Grade2',{ role: 'annotation' }, 'Grade3',{ role: 'annotation' }, 'Bal-Grade', 'Total', { role: 'annotation' } ],

enter image description here

google.charts.load('current', {
  packages: ['corechart', 'table', 'gauge', 'controls']
}).then(function () {
  drawMainDashboard();
});

function drawMainDashboard() {
  var dashboard = new google.visualization.Dashboard(
  document.getElementById('dashboard_division1'));

  var categoryPicker = new google.visualization.ControlWrapper({
  'controlType': 'CategoryFilter',
  'containerId': 'categoryPicker_div',
  'options': {
    'filterColumnIndex': 1,
    'ui': {
      'labelStacking': 'vertical',
      'label': 'Company Selection:',
      'allowTyping': false,
      'allowMultiple': false
    }
  }
  });

  var categoryPicker1 = new google.visualization.ControlWrapper({
  'controlType': 'CategoryFilter',
  'containerId': 'categoryPicker_div1',
  'options': {
    'filterColumnIndex': 0,
    'ui': {
      'labelStacking': 'vertical',
      'label': 'Location Selection:',
      'allowTyping': false,
      'allowMultiple': false
    }
  }
  });

  var columnchrt = new google.visualization.ChartWrapper({
  'chartType': 'ColumnChart',
  'containerId': 'chart_div',
  'options': {
       title: "Locations  charts",
       width: 850,
       height: 500,
       legend: { position: 'top', maxLines: 2 },
       bar: { groupWidth: '70%' },
       isStacked: true,
       focusTarget: 'category',
       aggregationTarget: 'category',
       explorer: {keepInBounds: true, maxZoomIn: 10.0}
  }
  });

  var table = new google.visualization.ChartWrapper({
  'chartType': 'Table',
  'containerId': 'table_div',
  'options': {
      showRowNumber: true,
      width: '100%',
      height: '100%',
      allowHtml: true
  },
  'view': {'columns': [0,1,2,3,4,5,6]}
  });

  google.visualization.events.addOneTimeListener(table, 'ready', calcTotals);
          
          function calcTotals() {
                // get filtered data table from table chart
                var dt = table.getDataTable();
                
                // build aggregation and view columns
                var aggColumns = [];
                var viewColumns = [0];
                for (var i = 2; i < dt.getNumberOfColumns(); i++) {
                      if (i !== dt.getNumberOfColumns() - 1) {
                        if (dt.getColumnType(i) === 'number') {
                          if (dt.getColumnRole(i) !== 'annotation') {
                            addAggColumn(i);
                            if (i !== dt.getNumberOfColumns() - 2) {
                              viewColumns.push(i - 1);
                            }
                          }
                        }
                      }
                    }
                function addAggColumn(index) {
                    aggColumns.push({
                      aggregation: google.visualization.data.sum,
                      column: index,
                      label: dt.getColumnLabel(index),
                      type: dt.getColumnType(index)
                    });
                  }
                // aggregate data table
                var agg = google.visualization.data.group(
                  dt,
                  [0],
                  aggColumns
                );
                var aggTotal = google.visualization.data.group(
                  dt,
                  [{
                    column: 0,
                    label: 'Total',
                    modifier: function (val) {
                      return 'Total';
                    },
                    type: 'string'
                  }],
                  aggColumns
                );
                
                dt = dt.toDataTable();
                dt.addRow(['Total', '--', aggTotal.getValue(0, 1), aggTotal.getValue(0, 2), aggTotal.getValue(0, 3), aggTotal.getValue(0, 4), aggTotal.getValue(0, 5), null]);
                table.setDataTable(dt);
                table.draw();
                
                viewColumns.push({
                    calc: function (dt, row) {
                      var total = 0;
                      for (var c = 1; c < dt.getNumberOfColumns(); c++) {
                        if (dt.getColumnLabel(c) !== 'Total') {
                          total += dt.getValue(row, c);
                        }
                      }
                      return total.toFixed(0);
                    },
                    role: 'annotation',
                    type: 'string'
                  });
                
                // create agg data view to add annotation
                var view = new google.visualization.DataView(agg);
                view.setColumns(viewColumns);
                view = view.toDataTable();
                for (var vc = 0; vc < view.getNumberOfColumns(); vc++) {
                  var viewLabel = view.getColumnLabel(vc);
                  for (var ac = 0; ac < agg.getNumberOfColumns(); ac++) {
                    var aggLabel = agg.getColumnLabel(ac);
                    if (viewLabel === aggLabel) {
                      view.setColumnLabel(vc, viewLabel + ' (' + aggTotal.getFormattedValue(0, ac) + ')');
                    }
                  }
                }
                
                // draw chart
                columnchrt.setDataTable(view);
                columnchrt.draw();

                google.visualization.events.addOneTimeListener(table, 'ready', calcTotals);
              
                
            }


  var data = google.visualization.arrayToDataTable([
    ['Location', 'Company', 'Grade1', 'Grade2', 'Grade3', 'Bal-Grade', 'Total', { role: 'annotation' } ],
    ['NYC', 'CUSTOMERS', 0, 0, 13, 5, 19, 29],
    ['CALI', 'ORG', 270, 210, 0, 32, 51, 60],
    ['CALI', 'CUSTOMERS', 35.942, 39, 0, 50, 126, 150],
    ['WDC', 'CUSTOMERS', 0, 0, 35, 52, 88, 100],
    ['WDC', 'CUSTOMERS', 44.507, 0, 25, 18, 88, 110],
    ['NJ', 'ORG', 0, 0, 54, 22, 28, 45],
    ['TXS', 'CUSTOMERS', 0, 0, 0, 10, 11, 20]
  ]);

  dashboard.bind([categoryPicker,categoryPicker1], [table]);
  dashboard.draw(data);
}
<script src="https://www.gstatic.com/charts/loader.js"></script>
<div id="dashboard_division" style="clear:left; display:inline-block; width:100%; float:left; margin-top:5px;">

<div class="float_left panel" style="float:left; width:50%; padding:0px;">
    <div id="chart_div"></div>
</div>
<div class="float_left panel" style="width:50%; padding:0px;">
    <div class="table_bbar" style="background-color:#27ae60;" >
        <div id="categoryPicker_div" style="right:15px; position:absolute;"></div>
        <div id="categoryPicker_div1" ></div>
    </div>

    <div id="table_div"></div>

</div>
</div>

Solution

  • to get the total row for the table chart,
    we can use a modifier function in the aggregation.

    we aggregate once by location,
    then again in total.

    the modifier function allows us to return the same value for every row,
    thus giving us the total for all rows.

    then we can add the results from the total agg to the location agg.

    // aggregate data table
var agg = google.visualization.data.group(
  dt,
  [0],
  aggColumns
);
var aggTotal = google.visualization.data.group(
  dt,
  [{
    column: 0,
    label: 'Total',
    modifier: function (val) {
      return 'Total';
    },
    type: 'string'
  }],
  aggColumns
);
var rowIndex = agg.addRow();
for (var i = 0; i < agg.getNumberOfColumns(); i++) {
  agg.setValue(rowIndex, i, aggTotal.getValue(0, i));
}

    then we can use the agg total data table to set the labels in the column chart legend.

    // create agg data view to add annotation
var view = new google.visualization.DataView(agg);
view.setColumns(viewColumns);
view = view.toDataTable();
for (var vc = 0; vc < view.getNumberOfColumns(); vc++) {
  var viewLabel = view.getColumnLabel(vc);
  for (var ac = 0; ac < agg.getNumberOfColumns(); ac++) {
    var aggLabel = agg.getColumnLabel(ac);
    if (viewLabel === aggLabel) {
      view.setColumnLabel(vc, viewLabel + ' (' + aggTotal.getFormattedValue(0, ac) + ')');
    }
  }
}

    one other slight change, since we are re-drawing the charts inside the 'ready' event,
    we must use a one time event, else we'll go into an endless loop.

    google.visualization.events.addOneTimeListener(table, 'ready', calcTotals);

    see following working snippet...

    google.charts.load('current', {
  packages: ['corechart', 'table', 'gauge', 'controls']
}).then(function () {
  drawMainDashboard();
});

function drawMainDashboard() {
  var dashboard = new google.visualization.Dashboard(
  document.getElementById('dashboard_division1'));

  var categoryPicker = new google.visualization.ControlWrapper({
  'controlType': 'CategoryFilter',
  'containerId': 'categoryPicker_div',
  'options': {
    'filterColumnIndex': 1,
    'ui': {
      'labelStacking': 'vertical',
      'label': 'Company Selection:',
      'allowTyping': false,
      'allowMultiple': false
    }
  }
  });

  var categoryPicker1 = new google.visualization.ControlWrapper({
  'controlType': 'CategoryFilter',
  'containerId': 'categoryPicker_div1',
  'options': {
    'filterColumnIndex': 0,
    'ui': {
      'labelStacking': 'vertical',
      'label': 'Location Selection:',
      'allowTyping': false,
      'allowMultiple': false
    }
  }
  });

  var columnchrt = new google.visualization.ChartWrapper({
  'chartType': 'ColumnChart',
  'containerId': 'chart_div',
  'options': {
       title: "Locations  charts",
       width: 850,
       height: 500,
       legend: { position: 'top', maxLines: 2 },
       bar: { groupWidth: '70%' },
       isStacked: true,
       explorer: {keepInBounds: true, maxZoomIn: 10.0}
  }
  });

  var table = new google.visualization.ChartWrapper({
  'chartType': 'Table',
  'containerId': 'table_div',
  'options': {
      showRowNumber: true,
      width: '100%',
      height: '100%',
      allowHtml: true
  },
  'view': {'columns': [0,1,2,3,4,5,6]}
  });

  google.visualization.events.addOneTimeListener(table, 'ready', calcTotals);

  function calcTotals() {
    // get filtered data table from table chart
    var dt = table.getDataTable();

    // build aggregation and view columns
    var aggColumns = [];
    var viewColumns = [0];
    for (var i = 2; i < dt.getNumberOfColumns(); i++) {
      if (i !== dt.getNumberOfColumns() - 1) {
        if (dt.getColumnType(i) === 'number') {
          if (dt.getColumnRole(i) !== 'annotation') {
            addAggColumn(i);
            if (i !== dt.getNumberOfColumns() - 2) {
              viewColumns.push(i - 1);
            }
          }
        }
      }
    }
    function addAggColumn(index) {
      aggColumns.push({
        aggregation: google.visualization.data.sum,
        column: index,
        label: dt.getColumnLabel(index),
        type: dt.getColumnType(index)
      });
    }

    // aggregate data table
    var agg = google.visualization.data.group(
      dt,
      [0],
      aggColumns
    );
    var aggTotal = google.visualization.data.group(
      dt,
      [{
        column: 0,
        label: 'Total',
        modifier: function (val) {
          return 'Total';
        },
        type: 'string'
      }],
      aggColumns
    );

    dt = dt.toDataTable();
    dt.addRow(['Total', '--', aggTotal.getValue(0, 1), aggTotal.getValue(0, 2), aggTotal.getValue(0, 3), aggTotal.getValue(0, 4), aggTotal.getValue(0, 5), null]);
    table.setDataTable(dt);
    table.draw();

    viewColumns.push({
      calc: function (dt, row) {
        var total = 0;
        for (var c = 1; c < dt.getNumberOfColumns(); c++) {
          if (dt.getColumnLabel(c) !== 'Total') {
            total += dt.getValue(row, c);
          }
        }
        return total.toFixed(0);
      },
      role: 'annotation',
      type: 'string'
    });

    // create agg data view to add annotation
    var view = new google.visualization.DataView(agg);
    view.setColumns(viewColumns);
    view = view.toDataTable();
    for (var vc = 0; vc < view.getNumberOfColumns(); vc++) {
      var viewLabel = view.getColumnLabel(vc);
      for (var ac = 0; ac < agg.getNumberOfColumns(); ac++) {
        var aggLabel = agg.getColumnLabel(ac);
        if (viewLabel === aggLabel) {
          view.setColumnLabel(vc, viewLabel + ' (' + aggTotal.getFormattedValue(0, ac) + ')');
        }
      }
    }

    // draw chart
    columnchrt.setDataTable(view);
    columnchrt.draw();

    google.visualization.events.addOneTimeListener(table, 'ready', calcTotals);
  }


  var data = google.visualization.arrayToDataTable([
    ['Location', 'Company', 'Grade1', 'Grade2', 'Grade3', 'Bal-Grade', 'Total', { role: 'annotation' } ],
    ['NYC', 'CUSTOMERS', 0, 0, 13, 5, 19, 19],
    ['CALI', 'ORG', 270, 210, 0, 32, 51, 51],
    ['CALI', 'CUSTOMERS', 35.942, 39, 0, 50, 126, 126],
    ['WDC', 'CUSTOMERS', 0, 0, 35, 52, 88, 88],
    ['WDC', 'CUSTOMERS', 44.507, 0, 25, 18, 88, 88],
    ['NJ', 'ORG', 0, 0, 54, 22, 28, 28],
    ['TXS', 'CUSTOMERS', 0, 0, 0, 10, 11, 11]
  ]);

  dashboard.bind([categoryPicker,categoryPicker1], [table]);
  dashboard.draw(data);
}
    <script src="https://www.gstatic.com/charts/loader.js"></script>
<div id="dashboard_division" style="clear:left; display:inline-block; width:100%; float:left; margin-top:5px;">

<div class="float_left panel" style="float:left; width:50%; padding:0px;">
    <div id="chart_div"></div>
</div>
<div class="float_left panel" style="width:50%; padding:0px;">
    <div class="table_bbar" style="background-color:#27ae60;" >
        <div id="categoryPicker_div" style="right:15px; position:absolute;"></div>
        <div id="categoryPicker_div1" ></div>
    </div>

    <div id="table_div"></div>

</div>
</div>