代码之家 › 专栏 › 技术社区 › user1577617

带有百分比和类别过滤器的谷歌条形图

bar-chart google-visualization charts javascript

1

user1577617 · 技术社区 · 8 年前

google.visualization.ChartWrapper Group Columns View

然而,当我尝试添加另一列(dataview)来计算总计时(这样我可以使用百分比绘制图表,并在条形标签中显示百分比),我的图表仍然在使用计数绘制。。谁能告诉我这里出了什么问题

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>

1 回复 | 直到 8 年前

1

WhiteHat 8 年前

对于初学者,建议使用较新的库 loader.js

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

而不是 jsapi release notes ...

谷歌图表的版本仍然可以通过 jsapi 加载程序不再持续更新。请从现在起使用新的gstatic loader。

load 语句,请参阅以下工作段。。。

接下来,没有看到数字格式化程序的定义
formatShort formatPercent

groupedData 将为您提供每个状态的总数

需要使用 modifier 作用
这会将值更改为 '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
    }]
  );

最后,删除 view 选项来自 barChart
我们要抽签

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>

要绘制百分比而不是计数,
看法

至于显示零值,
使用原始数据表获得状态值的不同列表
分组数据
如果不是,则添加状态行

  // 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);
  }
}

<脚本src=”https://www.gstatic.com/charts/loader.js“>lt;/script>
<div id=“proxyTable”></div>

更新2

要找到多项选择题的总数,

新列应测试所有问题列是否都为空
然后汇总计算列上的视图

请参阅以下工作段。。。

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>