Google Dashboard filter line chart by year

Question

I have a data table with number of car sales in the last 3 years. I want to create a line chart showing sales trend over a given year. I need a select element that will allow users to filter sales by year.

My table is has many columns including one for sale date (column 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']
  ]);

So far I have:

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

The datePicker get populated with the sale dates but I need the year. Also, the line chart doesn't render - I get an error: All series on a given axis must be of the same data type. Any pointers to a solution would be appreciated.

it would be helpful, if you could please share a sample of the data --> gDataTableSales — WhiteHat

WhiteHat WhiteHat · Accepted Answer · 2017-02-11T18:58:27

first, need to aggregate the counts for 'sale type'
(similar to this other answer)

once the data has been aggregated, format the date column as 'yyyy'

then use the following option in the 'CategoryFilter'
useFormattedValue: true

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

  // 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>

Google Dashboard filter line chart by year

1 Answers