Limit range to only show x months in Google Charts

Question

I am trying to limit the date range displayed in a line chart.

Current Chart:

I would like to limit the number of results shown to the last 3 months from today's date (current date) but I can't figure out how to do that. I have looked into using ChartRangeFilter on posts such as this one here, but this requires the user to do the filtering; I want the results to be limited as soon as the user loads the chart.

I have considered (not sure how to implement these):

1. limiting the ticks shown in Google Charts to last 3 ticks
2. filtering the last 3 months of the BigQuery JSON results using Python (before the results are sent to Google Charts)

JSON:

{
"rows": [{
    "c": [{
        "v": "2010-10"
    }, {
        "v": 5
    }]
}, {
    "c": [{
        "v": "2012-11"
    }, {
        "v": 43
    }]
}, {
    "c": [{
        "v": "2016-12"
    }, {
        "v": 59
    }]
}, {
    "c": [{
        "v": "2016-01"
    }, {
        "v": 50
    }]
}, {
    "c": [{
        "v": "2011-02"
    }, {
        "v": 37
    }]
}, {
    "c": [{
        "v": "2016-03"
    }, {
        "v": 38
    }]
}, {
    "c": [{
        "v": "2016-04"
    }, {
        "v": 46
    }]
}, {
    "c": [{
        "v": "2016-05"
    }, {
        "v": 45
    }]
}, {
    "c": [{
        "v": "2016-06"
    }, {
        "v": 22
    }]
}, {
    "c": [{
        "v": "2011-07"
    }, {
        "v": 10.0
    }]
}, {
    "c": [{
        "v": "2011-08"
    }, {
        "v": 50
    }]
}, {
    "c": [{
        "v": "2016-09"
    }, {
        "v": 7
    }]
}, {
    "c": [{
        "v": "2016-10"
    }, {
        "v": 14
    }]
}, {
    "c": [{
        "v": "2016-11"
    }, {
        "v": 55
    }]
}],
"cols": [{
    "type": "string",
    "id": "SalesDate",
    "label": "SaleDate"
}, {
    "type": "number",
    "id": "InventoryPercent",
    "label": "InventoryPercent"
}]
}

Desired Results:

//via Python - returns only these values to be displayed in chart
"v": "2016-09"    "v": 7
"v": "2016-10"    "v": 14
"v": "2016-11"    "v": 55

Desired Chart:

---

I have attempted the first approach but it doesn't work:

JavaScript:

$.get('/sales_data', function(response) {
    salesData = JSON.parse(response);
    // Set a callback to run when the Google Visualization API is loaded.
    google.charts.setOnLoadCallback(drawChart);
});

function drawChart() {
    //3 ticks for tick count
    function formatMonth() {
        var today = newDate();
        var salesMonth;
        for (var x = 2; x >= 0; x--) {
        salesMonth = moment(new Date(today.getFullYear(), today.getMonth() - x).format("YYYY-MM");
        console.log(salesMonth);
        }
    }

    var chartOptions = {
        vAxis: {
            minValue: 0,
            maxValue: 100,
            interpolateNulls: true,
            visible: true,
            gridlines: { count: 5},
            ticks: { //tick count - limit to last 3 months
                format: function() {
                return formatMonth();
            }
        }

How would I go about limiting the range to show only last 3 months?

Answer 1

it probably makes more sense to filter the data on the server / source,
which would reduce the amount of data handled by the client

however, google does provide methods to handle filtering, before drawing

you can use a combination of the setRows and getFilteredRows methods to create a DataView

see following working snippet...

first, a DataView is created from the json, which converts the first column to a real date

then setRows and getFilteredRows are used to limit to the last 3 months

then the dates from each row in the DataView are used for the hAxis ticks

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

function drawChart() {
  var dataTable = new google.visualization.DataTable({
    "rows": [{
        "c": [{
            "v": "2010-10"
        }, {
            "v": 5
        }]
    }, {
        "c": [{
            "v": "2012-11"
        }, {
            "v": 43
        }]
    }, {
        "c": [{
            "v": "2016-12"
        }, {
            "v": 59
        }]
    }, {
        "c": [{
            "v": "2016-01"
        }, {
            "v": 50
        }]
    }, {
        "c": [{
            "v": "2011-02"
        }, {
            "v": 37
        }]
    }, {
        "c": [{
            "v": "2016-03"
        }, {
            "v": 38
        }]
    }, {
        "c": [{
            "v": "2016-04"
        }, {
            "v": 46
        }]
    }, {
        "c": [{
            "v": "2016-05"
        }, {
            "v": 45
        }]
    }, {
        "c": [{
            "v": "2016-06"
        }, {
            "v": 22
        }]
    }, {
        "c": [{
            "v": "2011-07"
        }, {
            "v": 10.0
        }]
    }, {
        "c": [{
            "v": "2011-08"
        }, {
            "v": 50
        }]
    }, {
        "c": [{
            "v": "2016-09"
        }, {
            "v": 7
        }]
    }, {
        "c": [{
            "v": "2016-10"
        }, {
            "v": 14
        }]
    }, {
        "c": [{
            "v": "2016-11"
        }, {
            "v": 55
        }]
    }],
    "cols": [{
        "type": "string",
        "id": "SalesDate",
        "label": "SaleDate"
    }, {
        "type": "number",
        "id": "InventoryPercent",
        "label": "InventoryPercent"
    }]
    }
  );
  dataTable.sort([{column: 0, desc: true}]);

  var datePattern = 'yyyy-MM';
  var formatDate = new google.visualization.DateFormat({pattern: datePattern});

  var dataView = new google.visualization.DataView(dataTable);
  dataView.setColumns([
    // date column
    {
      calc: function (dt, row) {
        var dateParts = dt.getValue(row, 0).split('-');
        var dateValue = new Date(parseInt(dateParts[0]), parseInt(dateParts[1]) - 1, 1);
        return {
          v: dateValue,
          f: formatDate.formatValue(dateValue)
        }
      },
      type: 'date',
      label: dataTable.getColumnLabel(0)
    },
    // inventory
    1
  ]);

  var today = new Date();
  var last3Months = new Date(today.getFullYear(), today.getMonth() - 3, today.getDate());

  dataView.setRows(dataView.getFilteredRows([{
    column: 0,
    minValue: last3Months
  }]));

  var hAxisTicks = [];
  for (var i = 0; i < dataView.getNumberOfRows(); i++) {
    hAxisTicks.push(dataView.getValue(i, 0));
  }

  var chart = new google.visualization.LineChart(document.getElementById('chart_div'));
  chart.draw(dataView, {
    hAxis: {
      format: datePattern,
      ticks: hAxisTicks
    }
  });

  var table = new google.visualization.Table(document.getElementById('table_div'));
  table.draw(dataView);
}

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