How to create date range filter for Pivot Table
This article explains how to create a date range filter for a Pivot Table.
You can apply the date range filter by using the DataManager predicate class. The following filter operators are supported by the DataManager for applying the filter.
- greaterthan
- greaterthanorequal
- lessthan
- lessthanorequal
- equal
- notequal
- startswith
- endswith
- contains
The following code snippet demonstrates how to apply the range filter by using the DataManager predicate class.
Javascript
var firstValue = '1996-07-04T00:00:00.000Z';
var secondValue = '1996-09-13T00:00:00.000Z';
var daterangepicker = new ej.calendars.DateRangePicker({
close: renderGrid,
select: function (args) {
if (args.startDate) {
firstValue = args.startDate.toISOString();
}
if (args.endDate) {
secondValue = args.endDate.toISOString();
}
}
});
daterangepicker.appendTo('#daterangepicker');
//Here 'OrderDate' specifies the particular field name which is used for the filtering in the data set.
//'greaterthanorequal' specifies the condition.
//firstValue denotes start date.
// Boolean value denotes match case property.
var mPredicate = new ej.data.Predicate('OrderDate', 'greaterthanorequal', firstValue, true, true);
mPredicate.and('OrderDate', 'lessthanorequal', secondValue, true, true);
var remoteData;
new ej.data.DataManager(order).executeQuery(new ej.data.Query().where(mPredicate)).then(function (e) {
remoteData = e.result;
var pivotGridObj = new ej.pivotview.PivotView({
dataSource: {
data: remoteData,
expandAll: true,
filters: [],
formatSettings: [{ name: 'OrderDate', format: 'dd/MM/yyyy', type: 'date' }],
columns: [{ name: 'OrderDate' }],
rows: [{ name: 'OrderID' }],
values: [{ name: 'Freight' }]
},
showFieldList: true,
height: 300,
width: '100%',
gridSettings: { columnWidth: 120 }
});
pivotGridObj.appendTo('#PivotView1');
});
function renderGrid() {
var pivotGridObj = document.getElementById('PivotView1').ej2_instances[0];
var mPredicate = new ej.data.Predicate('OrderDate', 'greaterthanorequal', firstValue, true, true);
mPredicate.and('OrderDate', 'lessthanorequal', secondValue, true, true);
var remoteData;
new ej.data.DataManager(order).executeQuery(new ej.data.Query().where(mPredicate)).then(function (e) {
remoteData = e.result;
pivotGridObj.dataSource.data = remoteData;
});
}
</script>For the detailed filter option in DataManager, refer to the following document link.
https://ej2.syncfusion.com/javascript/documentation/data/querying.html#filtering
Refer to the working sample for additional details and implementation: Render-pivot tabel-by-using-date-range-filter