谷歌仪表板过滤线图

Question

我有一个数据表，上面有过去三年汽车销量的数字。我想要创建一个线图表显示销售趋势在一个给定的一年。我需要一个select元素，允许用户按年过滤销售。

我的表有许多列，包括一个待售日期(第0列)：

var dataTableSales = google.visualization.arrayToDataTable([
    ['Sale Date', 'Sale Type'],
    [new Date(2016, 0, 16), 'cash sale'],
    [new Date(2016, 0, 16), 'cash sale'],
    [new Date(2016, 0, 16), 'leased'],
    [new Date(2016, 0, 16), 'leased'],
    [new Date(2016, 0, 16), 'financed'],
    [new Date(2017, 0, 16), 'cash sale'],
    [new Date(2017, 0, 16), 'cash sale'],
    [new Date(2017, 0, 16), 'cash sale'],
    [new Date(2017, 0, 16), 'financed'],
    [new Date(2016, 0, 17), 'cash sale'],
    [new Date(2016, 0, 17), 'financed'],
    [new Date(2016, 0, 17), 'cash sale'],
    [new Date(2016, 0, 17), 'leased'],
    [new Date(2016, 0, 17), 'financed'],
    [new Date(2017, 0, 17), 'financed'],
    [new Date(2017, 0, 17), 'financed'],
    [new Date(2017, 0, 17), 'cash sale'],
    [new Date(2017, 0, 17), 'financed'],
    [new Date(2016, 0, 18), 'leased'],
    [new Date(2016, 0, 18), 'cash sale'],
    [new Date(2017, 0, 18), 'cash sale'],
    [new Date(2017, 0, 18), 'cash sale']
  ]);

到目前为止，我已经：

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

    // Create a line chart, passing some options
    var lineChart = new google.visualization.ChartWrapper({
        'chartType': 'LineChart',
        'containerId': 'chart_div',
        'options': {
        'width': 720,
        'height': 300
        }
    } );

    dashboard.bind( datePicker, lineChart );
    dashboard.draw( gDataTableSales );

datePicker上有销售日期，但我需要一年。另外，线图没有呈现-我得到一个错误:给定轴上的所有序列都必须是相同的数据类型。任何指向解决方案的指针都将不胜感激。

Answer 1

首先，需要聚合'sale type'的计数

(类似于this other answer)

将数据聚合后，将日期列格式化为'yyyy'

然后在'CategoryFilter'中使用以下选项

useFormattedValue: true

看下面的工作片段..。

​

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

function drawChart() {
  var dataTableSales = google.visualization.arrayToDataTable([
    ['Sale Date', 'Sale Type'],
    [new Date(2016, 0, 16), 'cash sale'],
    [new Date(2016, 0, 16), 'cash sale'],
    [new Date(2016, 0, 16), 'leased'],
    [new Date(2016, 0, 16), 'leased'],
    [new Date(2016, 0, 16), 'financed'],
    [new Date(2017, 0, 16), 'cash sale'],
    [new Date(2017, 0, 16), 'cash sale'],
    [new Date(2017, 0, 16), 'cash sale'],
    [new Date(2017, 0, 16), 'financed'],
    [new Date(2016, 0, 17), 'cash sale'],
    [new Date(2016, 0, 17), 'financed'],
    [new Date(2016, 0, 17), 'cash sale'],
    [new Date(2016, 0, 17), 'leased'],
    [new Date(2016, 0, 17), 'financed'],
    [new Date(2017, 0, 17), 'financed'],
    [new Date(2017, 0, 17), 'financed'],
    [new Date(2017, 0, 17), 'cash sale'],
    [new Date(2017, 0, 17), 'financed'],
    [new Date(2016, 0, 18), 'leased'],
    [new Date(2016, 0, 18), 'cash sale'],
    [new Date(2017, 0, 18), 'cash sale'],
    [new Date(2017, 0, 18), 'cash sale']
  ]);
  dataTableSales.sort({column: 0});

  // build view and aggregation columns
  var viewColumns = [{
    label: dataTableSales.getColumnLabel(0),
    type: dataTableSales.getColumnType(0),
    calc: function (dt, row) {
      return {
        v: dt.getValue(row, 0),
        f: dt.getValue(row, 0).getFullYear().toString()
      };
    }
  }];
  var aggColumns = [];
  var saleTypes = dataTableSales.getDistinctValues(1);
  saleTypes.forEach(function (saleType) {
    var colIndex = viewColumns.push({
      label: saleType,
      type: 'number',
      calc: function (dt, row) {
        return (dt.getValue(row, 1) === saleType) ? 1 : 0;
      }
    });
    aggColumns.push({
      aggregation: google.visualization.data.sum,
      column: colIndex - 1,
      label: saleType,
      type: 'number'
    });
  });

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

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

  var formatDate = new google.visualization.DateFormat({
    pattern: 'yyyy'
  });
  formatDate.format(summary, 0);

  var datePicker = new google.visualization.ControlWrapper({
    controlType: 'CategoryFilter',
    containerId: 'categoryFilter_div',
    options: {
      filterColumnIndex: 0,
      ui: {
        allowTyping: false,
        allowMultiple: false,
        label: 'Year:',
        labelStacking: 'vertical'
      },
      useFormattedValue: true
    }
  });

  var lineChart = new google.visualization.ChartWrapper({
    chartType: 'LineChart',
    containerId: 'chart_div',
    options: {
      width: 720,
      height: 300
    }
  });

  var dashboard = new google.visualization.Dashboard(document.getElementById('dashboard_div'));
  dashboard.bind(datePicker, lineChart);
  dashboard.draw(summary);
}

<script src="https://www.gstatic.com/charts/loader.js"></script>
<div id="dashboard_div">
  <div id="categoryFilter_div"></div>
  <div id="chart_div"></div>
</div>

​