CODEWITHSUNDEEP
javascriptgoogle-visualization
scripter78
scripter78

Reputation: 1177

Google Visualizations Grouping Calculations

I have a table that displays as expected showing each individual on the team but I want the Chart to show the Manager level (Team Level) total Stats. Currently it shows the Manager name for each of the team members I need to group the datatable by the manager name and sum the "SavedTest" and "MissedTest" numbers then Calculate the percentage for them. under the PERC column. I have viewed numerous articles and how tos but all of them only show how to do a single column calculation not multiples.

JSFiddle Example Here

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

google.charts.setOnLoadCallback(GetChartData);

 function GetChartData() {
var jsonData = {
  "cols": [
    {
      "type": "string",
      "id": "Division",
      "label": "Division"
    },
    {
      "type": "string",
      "id": "ManagerName",
      "label": "ManagerName"
    },
    {
      "type": "string",
      "id": "TechName",
      "label": "TechName"
    },
    {
      "type": "string",
      "id": "TechID",
      "label": "TechID"
    },
    {
      "type": "string",
      "id": "CableMonth",
      "label": "CableMonth"
    },
    {
      "type": "string",
      "id": "CableYear",
      "label": "CableYear"
    },
    {
      "type": "number",
      "id": "SavedTest",
      "label": "SavedTest"
    },
    {
      "type": "number",
      "id": "MissedTest",
      "label": "MissedTest"
    },
    {
      "type": "number",
      "id": "Perc",
      "label": "Perc"
    },
    {
      "type": "string",
      "id": "ManagerUserName",
      "label": "ManagerUserName"
    }
  ],
  "rows": [
    {
      "c": [
        {
          "v": "TPA"
        },
        {
          "v": "Bob Dobalina"
        },
        {
          "v": "Mike Hunt"
        },
        {
          "v": "74339"
        },
        {
          "v": "April"
        },
        {
          "v": "2016"
        },
        {
          "v": 35
        },
        {
          "v": 2
        },
        {
          "v": 0.95
        },
        {
          "v": "BDobalina"
        }
      ]
    },
    {
      "c": [
        {
          "v": "TPA"
        },
        {
          "v": "Bob Dobalina"
        },
        {
          "v": "Mike Hunt"
        },
        {
          "v": "74339"
        },
        {
          "v": "February"
        },
        {
          "v": "2016"
        },
        {
          "v": 28
        },
        {
          "v": 6
        },
        {
          "v": 0.82
        },
        {
          "v": "BDobalina"
        }
      ]
    },
    {
      "c": [
        {
          "v": "TPA"
        },
        {
          "v": "Bob Dobalina"
        },
        {
          "v": "Mike Hunt"
        },
        {
          "v": "74339"
        },
        {
          "v": "January"
        },
        {
          "v": "2016"
        },
        {
          "v": 43
        },
        {
          "v": 4
        },
        {
          "v": 0.91
        },
        {
          "v": "BDobalina"
        }
      ]
    },
    {
      "c": [
        {
          "v": "TPA"
        },
        {
          "v": "Bob Dobalina"
        },
        {
          "v": "Mike Hunt"
        },
        {
          "v": "74339"
        },
        {
          "v": "March"
        },
        {
          "v": "2016"
        },
        {
          "v": 45
        },
        {
          "v": 6
        },
        {
          "v": 0.88
        },
        {
          "v": "BDobalina"
        }
      ]
    },
    {
      "c": [
        {
          "v": "TPA"
        },
        {
          "v": "Bob Dobalina"
        },
        {
          "v": "Chris Christianson"
        },
        {
          "v": "72019"
        },
        {
          "v": "April"
        },
        {
          "v": "2016"
        },
        {
          "v": 41
        },
        {
          "v": 2
        },
        {
          "v": 0.95
        },
        {
          "v": "BDobalina"
        }
      ]
    },
    {
      "c": [
        {
          "v": "TPA"
        },
        {
          "v": "Bob Dobalina"
        },
        {
          "v": "Chris Christianson"
        },
        {
          "v": "72019"
        },
        {
          "v": "March"
        },
        {
          "v": "2016"
        },
        {
          "v": 34
        },
        {
          "v": 5
        },
        {
          "v": 0.87
        },
        {
          "v": "BDobalina"
        }
      ]
    },
    {
      "c": [
        {
          "v": "TPA"
        },
        {
          "v": "Bob Dobalina"
        },
        {
          "v": "Robert Bank"
        },
        {
          "v": "80862"
        },
        {
          "v": "May"
        },
        {
          "v": "2016"
        },
        {
          "v": 11
        },
        {
          "v": 5
        },
        {
          "v": 0.69
        },
        {
          "v": "BDobalina"
        }
      ]
    },
    {
      "c": [
        {
          "v": "TPA"
        },
        {
          "v": "Bob Dobalina"
        },
        {
          "v": "Tina Turner"
        },
        {
          "v": "82371"
        },
        {
          "v": "March"
        },
        {
          "v": "2016"
        },
        {
          "v": 42
        },
        {
          "v": 4
        },
        {
          "v": 0.91
        },
        {
          "v": "BDobalina"
        }
      ]
    }
  ]
};



    var data = new google.visualization.DataTable(jsonData);
      var formatter = new google.visualization.NumberFormat({
          fractionDigits: 2,
          suffix: '%'
      });

      formatter.format(data, 8);
      var table = new google.visualization.Table(document.getElementById('table_div'));
      var chart = new google.visualization.BarChart(document.getElementById('chart_div'));

      table.draw(data, { showRowNumber: true, width: '100%', height: '100%' });


        // Create a view that shows everyone hired since 2007.
      var view = new google.visualization.DataView(data);
      view.hideColumns([0,2,3,4,5,6,7,9]);
      chart.draw(view, { width: 900, height: 900, is3D: true, title: 'Test Bar Chart' });



  }




    function buildGoogleDataView(datar)
    {
        var data = new google.visualization.DataTable();
        data.addColumn('string', 'Market');
        data.addColumn('string', 'Manager');
        data.addColumn('string', 'Tech');
        data.addColumn('string', 'Tech ID');
        data.addColumn('string', 'WO#');
        data.addColumn('string', 'Account');
        data.addColumn('string', 'Date');
        data.addColumn('string', 'Cable Month');
        data.addColumn('string', 'Cable Year');
        data.addColumn('string', 'Manager');
        data.addColumn('int', 'Saved HHC');
        data.addRows(datar.length);
        var row
        for (r = 0; r < datar.length; r++)
        {
            for(c = 0;c<datar[r].length;c++)
            {
                data.setCell(r, c, datar[r][c]);
            }
        }

        var view = new google.visualization.DataView(data);
        view.setColumns([0, 1]);

        var table = new google.visualization.Table(document.getElementById('table_sort_div'));
        table.draw(view, { width: '100%', height: '100%' });


        var chart = new google.visualization.BarChart(document.getElementById('chart_sort_div'));
        chart.draw(view);

    }

Upvotes: 1

Views: 366

Answers (1)

WhiteHat
WhiteHat

Reputation: 61212

here's an example of aggregating multiple columns... 

// group data
var group = google.visualization.data.group(
  data,  // data table
  [1],   // group data by ManagerName
  [
    // agg columns
    {'column': 6, 'aggregation': google.visualization.data.sum, 'type': 'number'},
    {'column': 7, 'aggregation': google.visualization.data.sum, 'type': 'number'}
  ]
);

then you can create a view to use the agg columns in a calculation 

// create view over group
var view = new google.visualization.DataView(group);
view.setColumns([0, {
  calc: function (groupData, row) {
    return {
      v: (groupData.getValue(row, 1) / (groupData.getValue(row, 1) + groupData.getValue(row, 2))),
      f: (groupData.getValue(row, 1) / (groupData.getValue(row, 1) + groupData.getValue(row, 2))).toFixed(2) + '%'
    };
  },
  type: 'number'
}]);

once the 'ready' event fires on the table, you can add the total row to the table chart

see following example, taken from fiddle... 

google.charts.load('current', { packages: ['table','corechart'] });
google.charts.setOnLoadCallback(GetChartData);

function GetChartData() {
  var jsonData = {
    "cols": [
      {
        "type": "string",
        "id": "Division",
        "label": "Division"
      },
      {
        "type": "string",
        "id": "ManagerName",
        "label": "ManagerName"
      },
      {
        "type": "string",
        "id": "TechName",
        "label": "TechName"
      },
      {
        "type": "string",
        "id": "TechID",
        "label": "TechID"
      },
      {
        "type": "string",
        "id": "CableMonth",
        "label": "CableMonth"
      },
      {
        "type": "string",
        "id": "CableYear",
        "label": "CableYear"
      },
      {
        "type": "number",
        "id": "SavedTest",
        "label": "SavedTest"
      },
      {
        "type": "number",
        "id": "MissedTest",
        "label": "MissedTest"
      },
      {
        "type": "number",
        "id": "Perc",
        "label": "Perc"
      },
      {
        "type": "string",
        "id": "ManagerUserName",
        "label": "ManagerUserName"
      }
    ],
    "rows": [
      {
        "c": [
          {
            "v": "TPA"
          },
          {
            "v": "Bob Dobalina"
          },
          {
            "v": "Mike Hunt"
          },
          {
            "v": "74339"
          },
          {
            "v": "April"
          },
          {
            "v": "2016"
          },
          {
            "v": 35
          },
          {
            "v": 2
          },
          {
            "v": 0.95
          },
          {
            "v": "BDobalina"
          }
        ]
      },
      {
        "c": [
          {
            "v": "TPA"
          },
          {
            "v": "Bob Dobalina"
          },
          {
            "v": "Mike Hunt"
          },
          {
            "v": "74339"
          },
          {
            "v": "February"
          },
          {
            "v": "2016"
          },
          {
            "v": 28
          },
          {
            "v": 6
          },
          {
            "v": 0.82
          },
          {
            "v": "BDobalina"
          }
        ]
      },
      {
        "c": [
          {
            "v": "TPA"
          },
          {
            "v": "Bob Dobalina"
          },
          {
            "v": "Mike Hunt"
          },
          {
            "v": "74339"
          },
          {
            "v": "January"
          },
          {
            "v": "2016"
          },
          {
            "v": 43
          },
          {
            "v": 4
          },
          {
            "v": 0.91
          },
          {
            "v": "BDobalina"
          }
        ]
      },
      {
        "c": [
          {
            "v": "TPA"
          },
          {
            "v": "Bob Dobalina"
          },
          {
            "v": "Mike Hunt"
          },
          {
            "v": "74339"
          },
          {
            "v": "March"
          },
          {
            "v": "2016"
          },
          {
            "v": 45
          },
          {
            "v": 6
          },
          {
            "v": 0.88
          },
          {
            "v": "BDobalina"
          }
        ]
      },
      {
        "c": [
          {
            "v": "TPA"
          },
          {
            "v": "Bob Dobalina"
          },
          {
            "v": "Chris Christianson"
          },
          {
            "v": "72019"
          },
          {
            "v": "April"
          },
          {
            "v": "2016"
          },
          {
            "v": 41
          },
          {
            "v": 2
          },
          {
            "v": 0.95
          },
          {
            "v": "BDobalina"
          }
        ]
      },
      {
        "c": [
          {
            "v": "TPA"
          },
          {
            "v": "Bob Dobalina"
          },
          {
            "v": "Chris Christianson"
          },
          {
            "v": "72019"
          },
          {
            "v": "March"
          },
          {
            "v": "2016"
          },
          {
            "v": 34
          },
          {
            "v": 5
          },
          {
            "v": 0.87
          },
          {
            "v": "BDobalina"
          }
        ]
      },
      {
        "c": [
          {
            "v": "TPA"
          },
          {
            "v": "Bob Dobalina"
          },
          {
            "v": "Robert Bank"
          },
          {
            "v": "80862"
          },
          {
            "v": "May"
          },
          {
            "v": "2016"
          },
          {
            "v": 11
          },
          {
            "v": 5
          },
          {
            "v": 0.69
          },
          {
            "v": "BDobalina"
          }
        ]
      },
      {
        "c": [
          {
            "v": "TPA"
          },
          {
            "v": "Bob Dobalina"
          },
          {
            "v": "Tina Turner"
          },
          {
            "v": "82371"
          },
          {
            "v": "March"
          },
          {
            "v": "2016"
          },
          {
            "v": 42
          },
          {
            "v": 4
          },
          {
            "v": 0.91
          },
          {
            "v": "BDobalina"
          }
        ]
      }
    ]
  };

  var data = new google.visualization.DataTable(jsonData);

  // group data
  var group = google.visualization.data.group(
    data,  // data table
    [1],   // group data by ManagerName
    [
      // agg columns
      {'column': 6, 'aggregation': google.visualization.data.sum, 'type': 'number'},
      {'column': 7, 'aggregation': google.visualization.data.sum, 'type': 'number'}
    ]
  );

  // create view over group
  var view = new google.visualization.DataView(group);
  view.setColumns([0, {
    calc: function (groupData, row) {
      return {
        v: (groupData.getValue(row, 1) / (groupData.getValue(row, 1) + groupData.getValue(row, 2))),
        f: (groupData.getValue(row, 1) / (groupData.getValue(row, 1) + groupData.getValue(row, 2))).toFixed(2) + '%'
      };
    },
    type: 'number'
  }]);


  var formatter = new google.visualization.NumberFormat({
      fractionDigits: 2,
      suffix: '%'
  });

  formatter.format(data, 8);
  var container = document.getElementById('table_div');
  var table = new google.visualization.Table(container);

  // table 'ready' event
  google.visualization.events.addListener(table, 'ready', function () {
    // add total row for each group row
    for (var i = 0; i < group.getNumberOfRows(); i++) {
      var totalRow = container.getElementsByTagName('TBODY')[0].insertRow(-1);

      // add total row cell for each header cell
      Array.prototype.forEach.call(container.getElementsByTagName('TR')[0].cells, function(cell) {
        var totalCell = totalRow.insertCell();
        switch (totalCell.cellIndex) {
          case 1:
            totalCell.innerHTML = 'Total';
            break;

          case 2:
            totalCell.innerHTML = group.getValue(i, 0);
            break;

          case 7:
            totalCell.innerHTML = group.getValue(i, 1);
            totalCell.style.textAlign = 'right';
            break;

          case 8:
            totalCell.innerHTML = group.getValue(i, 2);
            totalCell.style.textAlign = 'right';
            break;

          case 9:
            totalCell.innerHTML = (group.getValue(i, 1) / (group.getValue(i, 1) + group.getValue(i, 2))).toFixed(2) + '%';
            totalCell.style.textAlign = 'right';
            break;
        }
      });
    }
  });

  var chart = new google.visualization.BarChart(document.getElementById('chart_div'));

  table.draw(data, { showRowNumber: true, width: '100%', height: '100%' });


  // Create a view that shows everyone hired since 2007.
  chart.draw(view, { width: 900, height: 900, is3D: true, title: 'Test Bar Chart' });
}
<script src="https://www.gstatic.com/charts/loader.js"></script>
<div id="chart_div"></div>
<div id="table_div"></div>

Upvotes: 2

Related Questions