javascript - 带有总百分比和类别过滤器的 Google 条形图

Question

我是 Google Charts 的新手，我正在尝试创建一个包含总计百分比的条形图，以及使用 Google 仪表板控件过滤数据的能力……我遵循了这个(感谢 @asgallant 的帮助! ) google.visualization.ChartWrapper Group Columns View并且能够得到一个条形图，它从谷歌表格中获取数据，并绘制带有计数的图表，并且还能够使用谷歌类别过滤器过滤数据。

但是，这就是我卡住的地方 - 当我尝试添加另一列(数据 View )来计算总数时(以便我可以使用百分比绘制图表并在条形标签中显示百分比) - 我的图表仍在使用计数进行绘图。任何人都可以让我知道我在这里出了什么问题:

function drawVisualization() {

    var query = new google.visualization.Query('https://docs.google.com/spreadsheets/d/1LBoS8Q7qdpWVjks3FytQAefThzY3VbAHllf04nE6qO8/edit?gid=1629614877&range=A:D');
    query.send(handleQueryResponse);

    function handleQueryResponse(response) {
     if (response.isError()) {return; }
    var data = response.getDataTable();


        // Define category pickers for All Filters
        var CardTier = new google.visualization.ControlWrapper({
            'controlType': 'CategoryFilter',
            'containerId': 'control1',
            'options': {
                'filterColumnLabel': 'CardTier Filter',
                'ui': {
                    'labelStacking': 'vertical',
                    'allowTyping': false,
                    'allowMultiple': false
                }
            }
        });

        var Campaign = new google.visualization.ControlWrapper({
            'controlType': 'CategoryFilter',
            'containerId': 'control2',
            'options': {
                'filterColumnLabel': 'Campaign Filter',
                'ui': {
                    'labelStacking': 'vertical',
                    'allowTyping': false,
                    'allowMultiple': false
                }
            }
        });


        // Define a bar chart to show 'Population' data
        var barChart = new google.visualization.ChartWrapper({
            'chartType': 'BarChart',
            'containerId': 'chart1',
            'options': {
                'width': 400,
                'height': 300,
                'chartArea': {top: 0, right: 0, bottom: 0}
            },
            // Configure the barchart to use columns 0 (Card Tier) and 1 (Campaign Filter) (Basically the filters)
            'view': {'columns': [0, 1]}
        });

        var proxyTable = new google.visualization.ChartWrapper({
            chartType: 'Table',
            containerId: 'proxyTable',
            options: {
                // minimize the footprint of the table in HTML
                page: 'enable',
                pageSize: 1
            },
            view: {
                columns: [0]
            }
        });



        // create a "ready" event handler for proxyTable the handles data aggregation and drawing barChart
        // Add The question's column index here. We want to draw Status so we Group 2 with dt and also its count...
        google.visualization.events.addListener(proxyTable, 'ready', function () {
            var dt = proxyTable.getDataTable();
            var groupedData = google.visualization.data.group(dt, [2], [{
                column: 3,
                type: 'number',
                label: dt.getColumnLabel(2),
                aggregation: google.visualization.data.count
            }]);

      var view = new google.visualization.DataView(groupedData);
      view.setColumns([0, 1, {
        calc: function (dt, row) {
          var amount =  formatShort.formatValue(dt.getValue(row, 1));
          var percent = formatPercent.formatValue(dt.getValue(row, 1) / groupedData.getValue(0, 1));
          return amount + ' (' + percent + ')';
        },
        type: 'string',
        role: 'annotation'
      }]);


            // after grouping, the data will be sorted by column 0, then 1, then 2
            // if you want a different order, you have to re-sort
            barChart.setDataTable(view);
            barChart.draw();

    });



        // Create the dashboard.
        new google.visualization.Dashboard(document.getElementById('dashboard')).
        // Configure the controls :
        bind(CardTier, Campaign).
        bind(Campaign, proxyTable).
        // Draw the dashboard
        draw(data);
    }
    }

    google.load('visualization', '1', {packages:['corechart', 'controls', 'table'], callback: drawVisualization});




    </script>
    </head>


      <body>

    <div id="dashboard">
        <table>
            <tr style='vertical-align: top'>
                <td style='width: 300px; font-size: 0.9em;'>
                    <div id="control1"></div>
                    <div id="control2"></div>
                </td>
                <td style='width: 600px'>
                    <div style="float: left;" id="chart1"></div>
                    <div style="float: left;" id="chart2"></div>
                </td>
            </tr>
        </table>
        <div id="proxyTable" style="display: none;"></div>
    </div>

    </body>  
    </html>

Answer 1

对于初学者，推荐使用较新的库 loader.js

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

而不是 jsapi，根据 release notes ...

The version of Google Charts that remains available via the jsapi loader is no longer being updated consistently. Please use the new gstatic loader from now on.

这只会更改 load 语句，请参阅以下工作片段...

---

接下来，没看到数字格式化器的定义
formatShort 和 formatPercent
需要添加那些

---

groupedData 将为您提供每个状态的总数
获取所有行的总数，
需要使用修饰符函数
这会将所有行的第一列的值更改为 'Total'
允许 group 方法聚合所有行

  var totalData = google.visualization.data.group(
    dataTable,
    [{column: 0, type: 'string', modifier: function () {return 'Total';}}],
    [{
      column: 3,
      type: 'number',
      label: dataTable.getColumnLabel(2),
      aggregation: google.visualization.data.count
    }]
  );

---

最后，从 barChart
中删除 view 选项因为我们提供了我们想要绘制的view

---

请参阅以下工作片段...

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

function drawVisualization() {
  var query = new google.visualization.Query('https://docs.google.com/spreadsheets/d/1LBoS8Q7qdpWVjks3FytQAefThzY3VbAHllf04nE6qO8/edit?gid=1629614877&range=A:D');
  query.send(handleQueryResponse);

  function handleQueryResponse(response) {
    if (response.isError()) {return; }
    var data = response.getDataTable();

    // Define category pickers for All Filters
    var CardTier = new google.visualization.ControlWrapper({
      'controlType': 'CategoryFilter',
      'containerId': 'control1',
      'options': {
        'filterColumnLabel': 'CardTier Filter',
        'ui': {
          'labelStacking': 'vertical',
          'allowTyping': false,
          'allowMultiple': false
        }
      }
    });

    var Campaign = new google.visualization.ControlWrapper({
      'controlType': 'CategoryFilter',
      'containerId': 'control2',
      'options': {
        'filterColumnLabel': 'Campaign Filter',
        'ui': {
          'labelStacking': 'vertical',
          'allowTyping': false,
          'allowMultiple': false
        }
      }
    });

    // Define a bar chart to show 'Population' data
    var barChart = new google.visualization.ChartWrapper({
      'chartType': 'BarChart',
      'containerId': 'chart1',
      'options': {
        'width': 400,
        'height': 300,
        'chartArea': {top: 0, right: 0, bottom: 0}
      }
    });

    var proxyTable = new google.visualization.ChartWrapper({
      chartType: 'Table',
      containerId: 'proxyTable',
      options: {
        // minimize the footprint of the table in HTML
        page: 'enable',
        pageSize: 1
      },
      view: {
        columns: [0]
      }
    });

    // create a "ready" event handler for proxyTable the handles data aggregation and drawing barChart
    // Add The question's column index here. We want to draw Status so we Group 2 with dt and also its count...
    google.visualization.events.addListener(proxyTable, 'ready', function () {
      var formatShort = new google.visualization.NumberFormat({
        pattern: 'short'
      });
      var formatPercent = new google.visualization.NumberFormat({
        pattern: '0.0%'
      });
      var dataTable = proxyTable.getDataTable();

      // group by status
      var groupedData = google.visualization.data.group(
        dataTable,
        [2],
        [{
          column: 3,
          type: 'number',
          label: dataTable.getColumnLabel(2),
          aggregation: google.visualization.data.count
        }]
      );

      // status total
      var totalData = google.visualization.data.group(
        dataTable,
        [{column: 0, type: 'string', modifier: function () {return 'Total';}}],
        [{
          column: 3,
          type: 'number',
          label: dataTable.getColumnLabel(2),
          aggregation: google.visualization.data.count
        }]
      );

      var view = new google.visualization.DataView(groupedData);
      view.setColumns([0, 1, {
        calc: function (dt, row) {
          var amount =  dt.getValue(row, 1);
          var total = totalData.getValue(0, 1);
          var percent = 0;
          if (total > 0) {
            percent = amount / total;
          }
          return formatShort.formatValue(amount) + ' (' + formatPercent.formatValue(percent) + ')';
        },
        type: 'string',
        role: 'annotation'
      }]);

      // after grouping, the data will be sorted by column 0, then 1, then 2
      // if you want a different order, you have to re-sort
      barChart.setDataTable(view);
      barChart.draw();
    });

    // Create the dashboard.
    new google.visualization.Dashboard(document.getElementById('dashboard')).
    // Configure the controls :
    bind(CardTier, Campaign).
    bind(Campaign, proxyTable).
    // Draw the dashboard
    draw(data);
  }
}

<script src="https://www.gstatic.com/charts/loader.js"></script>
<div id="control1"></div>
<div id="control2"></div>
<div id="chart1"></div>
<div id="proxyTable"></div>

---

更新

绘制百分比而不是计数，
只需要将另一个计算列添加到 view

至于显示零值，
使用原始数据表获取不同的状态值列表
检查状态是否存在于 groupedData
如果不是，为状态添加一行

  // add back missing status
  var statusValues = data.getDistinctValues(2);
  statusValues.forEach(function (status) {
    var statusRow = groupedData.getFilteredRows([{
      column: 0,
      value: status
    }]);
    if (statusRow.length === 0) {
      groupedData.addRow([
        status,
        0
      ]);
    }
  });
  groupedData.sort([{column: 0}]);

请参阅以下工作片段...

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

function drawVisualization() {
  var query = new google.visualization.Query('https://docs.google.com/spreadsheets/d/1LBoS8Q7qdpWVjks3FytQAefThzY3VbAHllf04nE6qO8/edit?gid=1629614877&range=A:D');
  query.send(handleQueryResponse);

  function handleQueryResponse(response) {
    if (response.isError()) {return; }
    var data = response.getDataTable();

    // Define category pickers for All Filters
    var CardTier = new google.visualization.ControlWrapper({
      'controlType': 'CategoryFilter',
      'containerId': 'control1',
      'options': {
        'filterColumnLabel': 'CardTier Filter',
        'ui': {
          'labelStacking': 'vertical',
          'allowTyping': false,
          'allowMultiple': false
        }
      }
    });

    var Campaign = new google.visualization.ControlWrapper({
      'controlType': 'CategoryFilter',
      'containerId': 'control2',
      'options': {
        'filterColumnLabel': 'Campaign Filter',
        'ui': {
          'labelStacking': 'vertical',
          'allowTyping': false,
          'allowMultiple': false
        }
      }
    });

    // Define a bar chart to show 'Population' data
    var barChart = new google.visualization.ChartWrapper({
      'chartType': 'BarChart',
      'containerId': 'chart1',
      'options': {
        'width': 400,
        'height': 300,
        'chartArea': {top: 0, right: 0, bottom: 0}
      }
    });

    var proxyTable = new google.visualization.ChartWrapper({
      chartType: 'Table',
      containerId: 'proxyTable',
      options: {
        // minimize the footprint of the table in HTML
        page: 'enable',
        pageSize: 1
      },
      view: {
        columns: [0]
      }
    });

    // create a "ready" event handler for proxyTable the handles data aggregation and drawing barChart
    // Add The question's column index here. We want to draw Status so we Group 2 with dt and also its count...
    google.visualization.events.addListener(proxyTable, 'ready', function () {
      var formatShort = new google.visualization.NumberFormat({
        pattern: 'short'
      });
      var formatPercent = new google.visualization.NumberFormat({
        pattern: '0.0%'
      });
      var dataTable = proxyTable.getDataTable();

      // group by status
      var groupedData = google.visualization.data.group(
        dataTable,
        [2],
        [{
          column: 3,
          type: 'number',
          label: dataTable.getColumnLabel(2),
          aggregation: google.visualization.data.count
        }]
      );

      // add back missing status
      var statusValues = data.getDistinctValues(2);
      statusValues.forEach(function (status) {
        var statusRow = groupedData.getFilteredRows([{
          column: 0,
          value: status
        }]);
        if (statusRow.length === 0) {
          groupedData.addRow([
            status,
            0
          ]);
        }
      });
      groupedData.sort([{column: 0}]);

      // status total
      var totalData = google.visualization.data.group(
        dataTable,
        [{column: 0, type: 'string', modifier: function () {return 'Total';}}],
        [{
          column: 3,
          type: 'number',
          label: dataTable.getColumnLabel(2),
          aggregation: google.visualization.data.count
        }]
      );

      var view = new google.visualization.DataView(groupedData);
      view.setColumns([0, {
        calc: function (dt, row) {
          var amount =  dt.getValue(row, 1);
          var total = totalData.getValue(0, 1);
          var percent = 0;
          if (total > 0) {
            percent = amount / total;
          }
          return {
            v: percent,
            f: formatPercent.formatValue(percent)
          };
        },
        type: 'number',
        label: 'Percent'
      }, {
        calc: function (dt, row) {
          var amount =  dt.getValue(row, 1);
          var total = totalData.getValue(0, 1);
          var percent = 0;
          if (total > 0) {
            percent = amount / total;
          }
          return formatPercent.formatValue(percent) + ' (' + formatShort.formatValue(amount) + ')';
        },
        type: 'string',
        role: 'annotation'
      }]);

      // after grouping, the data will be sorted by column 0, then 1, then 2
      // if you want a different order, you have to re-sort
      barChart.setDataTable(view);
      barChart.draw();
    });

    // Create the dashboard.
    new google.visualization.Dashboard(document.getElementById('dashboard')).
    // Configure the controls :
    bind(CardTier, Campaign).
    bind(Campaign, proxyTable).
    // Draw the dashboard
    draw(data);
  }
}

<script src="https://www.gstatic.com/charts/loader.js"></script>
<div id="control1"></div>
<div id="control2"></div>
<div id="chart1"></div>
<div id="proxyTable"></div>

---

更新 2

求一道选择题的总分，
创建一个带有计算列的 View
新列应测试所有问题列均不为空
然后总计计算列上的 View

请参阅以下工作片段...

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

function drawVisualization() {
  var query = new google.visualization.Query('https://docs.google.com/spreadsheets/d/19VWNZkHG5GEuYCibDmtOlKblKiOWcx94Wi9jyuhvEUo/edit#gid=0');
  query.setQuery('select A,B,C,D,E,F,G');
  query.send(handleQueryResponse);

  function handleQueryResponse(response) {
    if (response.isError()) {return;}
    var data = response.getDataTable();

    var view = new google.visualization.DataView(data);
    view.setColumns([0, 1, 2, 3, 4, 5, 6, {
      calc: function (dt, row) {
        var answered = 0;
        var q1_1 = dt.getValue(row, 3) || '';
        var q1_2 = dt.getValue(row, 4) || '';
        var q1_3 = dt.getValue(row, 5) || '';
        var q1_4 = dt.getValue(row, 6) || '';
        if ((q1_1 !== '') || (q1_2 !== '') || (q1_3 !== '') || (q1_4 !== '')) {
          answered = 1;
        }
        return answered;
      },
      label: 'Answered',
      type: 'number'
    }]);

    var totalAnswered = google.visualization.data.group(
      view,
      [{column: 0, type: 'string', modifier: function () {return 'Total';}}],
      [{
        column: view.getNumberOfColumns() - 1,
        type: 'number',
        label: view.getColumnLabel(view.getNumberOfColumns() - 1),
        aggregation: google.visualization.data.sum
      }]
    );

    var proxyTable = new google.visualization.ChartWrapper({
      chartType: 'Table',
      containerId: 'proxyTable',
      dataTable: view
    });
    proxyTable.draw();

    document.getElementById('proxyTableTotal').innerHTML = 'Total Answered = ' + totalAnswered.getValue(0, 1);
  }
}

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