How to filter Pie Chart using date in Google Visualization Dashboard

Question

I have a data group taken from a table of sales for the year. The group has two columns: Sale Type (e.g. Cash, Lease, etc.), and Count which is just an aggregation on the date field.

I can get a pie chart easy enough. Now I want to add a category picker that will allow the user to change the pie chart by picking a year. How can I do that?

Here is my code so far:

var dashboard = new google.visualization.Dashboard(
    document.getElementById( 'dashboard_div' ) );

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

var groupedData = google.visualization.data.group(
    gDataTableSales,
    [ { column: 3, type: 'string', label: 'Type' } ],
    [ { column: 2, aggregation: google.visualization.data.count, type: 'number', label: 'Count' } ] );

var chart = new google.visualization.ChartWrapper({
    chartType: 'PieChart',
    containerId: 'chart_div' });

dashboard.bind( [ categoryPicker ], [ chart ] );
dashboard.draw( groupedData );

The chart and category picker get rendered and I can select the count and the chart is updated suggesting that the mechanics are working as expected.

WhiteHat WhiteHat · Accepted Answer · 2017-05-12T17:03:29

the data format for a PieChart is different from the other core charts
the data needs to be in rows, rather than columns
there can only be one series

one option would be to use two aggregations...

Year, Category, Count
Category, Count

the second is only needed if you want to allow selection of "All Years", or
use --> allowNone: true -- which is the default

instead of binding the category filter and chart in a dashboard, draw them independently

use the filter's statechange event to determine which data table to draw

see following working snippet...

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

  var dataByYear = google.visualization.data.group(
    dataTableSales,
    [{
      column: 0,
      type: 'string',
      modifier: function (value) {
        return value.getFullYear().toString();
      }
    }, 1],
    [{
      column: 1,
      type: 'number',
      aggregation: google.visualization.data.count
    }]
  );

  var dataAll = google.visualization.data.group(
    dataTableSales,
    [1],
    [{
      column: 1,
      type: 'number',
      aggregation: google.visualization.data.count
    }]
  );

  var yearPicker = new google.visualization.ControlWrapper({
    controlType: 'CategoryFilter',
    containerId: 'categoryFilter_div',
    dataTable: dataByYear,
    options: {
      filterColumnIndex: 0,
      ui: {
        allowTyping: false,
        allowMultiple: false,
        caption: 'All Years',
        label: '',
        labelStacking: 'vertical'
      },
      useFormattedValue: true
    }
  });
  google.visualization.events.addListener(yearPicker, 'statechange', function () {
    if (yearPicker.getState().selectedValues.length > 0) {
      pieChart.setView({
        columns: [1, 2],
        rows: dataByYear.getFilteredRows([{
          column: 0,
          value: yearPicker.getState().selectedValues[0]
        }])
      });
      pieChart.setDataTable(dataByYear);
    } else {
      pieChart.setView(null);
      pieChart.setDataTable(dataAll);
    }
    pieChart.draw();
  });
  yearPicker.draw();

  var pieChart = new google.visualization.ChartWrapper({
    chartType: 'PieChart',
    containerId: 'chart_div',
    dataTable: dataAll,
    options: {
      height: 300
    }
  });
  pieChart.draw();
}

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

How to filter Pie Chart using date in Google Visualization Dashboard

1 Answers