Filter

You can apply certain filters to a pivot table to view only the required information while hiding the rest of the data.

Row and Column Fields Filter

You can apply the Row and Column Fields filter when the data of rows or columns need to be filtered. The following image shows the filter applied on a pivot table to show the quantity for the only East region.

The following code sample shows how to filter the Region column to show only the East region's data.

// add filter field
myPivotTable.add("OrderDate", "OrderDate", GC.Spread.Pivot.PivotTableFieldType.filterField);
let itemList = ["East"];
myPivotTable.labelFilter("Region", { textItem: { list: itemList, isAll: false } });

Value Filter

The value filter can be used to filter the value-based fields in a pivot table based on summary values (like sum, count, max, min, average, etc.). The following image shows the value filter applied on a pivot table to show the 'Sum of quantity' of the Category column with values greater than 2000.

The following code sample shows how to apply a value filter to display the 'sum of quantity' greater than 2000.

// add filter field
myPivotTable.add("OrderDate", "OrderDate", GC.Spread.Pivot.PivotTableFieldType.filterField);
// apply Value Filter for "Sum of quantity" greater than 2000 using FilterInfo
let valueFilter = { condition: { conType: GC.Pivot.PivotConditionType.value, val: [2000], operator: 2 }, conditionByName: "Sum of quantity" };
myPivotTable.valueFilter("Category", valueFilter);

Label Filter

The label filter can be used to filter row label items. It provides different options based on the data type of the field to be filtered. For example, for string or numeric data type, the options provided are equals, does not equal, begin with, contains, greater than, etc. whereas for date data type, the options provided are before, after, tomorrow, next week, etc. The following example demonstrates how to apply a label filter to the "Region" column field to display a city that starts with “S”.

The following code sample shows how to apply a label filter for a city that starts with "S".

// add filter field
myPivotTable.add("OrderDate", "OrderDate", GC.Spread.Pivot.PivotTableFieldType.filterField);
// apply Label Filter for 'City' names starting with 'S'using FilterInfo
let labelFilter = { condition: { conType: GC.Pivot.PivotConditionType.caption, val: 'S', operator: 2 } };
myPivotTable.labelFilter("City", labelFilter);

You can also use the manual filter in place of defining conditions for the label filter. The following image shows a manual filter applied to display the data for 'Jersey' and 'Seattle' cities.

The following code sample shows how to apply a manual filter.

// add manual filter
let parent = { textItem: { list: ["Jersey", "Seattle"], isAll: false } };
myPivotTable.labelFilter("City", parent);

You can also choose to apply manual and condition-based label filters together by setting the allowMultipleFiltersPerField option to true. The following example demonstrates how to apply multiple filters in a pivot table.

The following code sample shows how to apply multiple filters in a pivot table.

myPivotTable.options.allowMultipleFiltersPerField = true;
// When allowMultipleFiltersPerField is set, the two filters i.e. both take effect
var labelFilter = {
    textItem: { list: ["Jersey", "San Francisco"], isAll: false },
    condition: { conType: GC.Pivot.PivotConditionType.caption, operator: GC.Pivot.PivotCaptionFilterOperator.beginsWith, val: 'S' }
};
myPivotTable.labelFilter("City", labelFilter);

Date Type Filter

A date-type filter can be used to specify criteria that are applied to date-type items of the pivot table. The following image shows the date type filter applied on a pivot table to show quarterly data.

The following code sample shows how to apply a date type filter.

let groupInfo = {
     originFieldName: "OrderDate",
     dateGroups: [
         { by: GC.Pivot.DateGroupType.quarters },
         { by: GC.Pivot.DateGroupType.years }]
 };
 myPivotTable.group(groupInfo);
 myPivotTable.add("OrderDate", "Qtr", GC.Spread.Pivot.PivotTableFieldType.columnField);
// apply label Filter using FilterInfo
 let labelFilter = { condition: { conType: GC.Pivot.PivotConditionType.date, val: [], operator: GC.Pivot.PivotDateFilterOperator.Q1 } };
 myPivotTable.labelFilter("Qtr", labelFilter);       

You can also use the below built-in date-to-date filters:

• Quarter-to-Date (QTD): Period starting at the beginning of the current quarter and ending on the current date.

• Month-to-Date (MTD): Period starting at the beginning of the current calendar month and ending on the current date.

• Year-to-Date (YTD): Period starting at the beginning of the current year and ending on the current date.

Along with the above options, you can use parallel date filters by setting the isParallel property to true. The parallel date filter allows you to get the same period in the previous or the upcoming dates (depending on the data in the pivot table) corresponding to the actual period. For example, suppose today is Nov 23, 2021, and the pivot table contains the data from Sep 1, 2021, to Dec 31, 2021.

Month to Date filter:

• Nov 1, 2021 - Nov 23,2021

Parallel month to date filter:

• Sep 1, 2021 - Sep 23, 2021

• Oct 1, 2021 - Oct 23, 2021

• Nov 1, 2021 - Nov 23, 2021

• Dec 1, 2021 - Dec 23, 2021

The following code sample shows how to apply a date-to-date filter.

function DateToDateFilter() {
    var condition = {
        conType: GC.Pivot.PivotConditionType.month,
        operator: GC.Pivot.PivotDateFilterOperator.dateToDate,
        val: [new Date(2021, 7, 15), new Date(2022, 6, 20)],
        isParallel: false,
        isDynamicEndDate: true,
        by: GC.Pivot.PivotAdvancedDateFilterBy.month
    };
    var filterInfo = {
        condition
    };
    myPivotTable.labelFilter("OrderDate", filterInfo);

}

Create FilterInfo

The above filters are applied by creating the filter info. The following table describes the conditions which can be used to create filter info:

Hide Filter Button

You can hide the filter buttons from the pivot table using the showFilter option.

The following example code shows how to hide filter buttons.

// Set showFilter to false
myPivotTable.options.showFilter = false;

Filter Dialog

You can use the Filter dialog by clicking on a column header in a pivot table. It provides various sorting and filtering options. You need to select the required items from the filter dialog's list box for applying the filter. The below image shows the filter dialog box.

The filter dialog also provides a search box that allows you to apply filtering settings conveniently.

You can input case-insensitive search items and also use the 'Add current selection to filter' option to merge the selected item with the previous filter information. If unchecked, the current selection overwrites the previous filter information.

It also allows you to search using regex characters like: '?', '*' and '~' as shown in the image below.

You can also use a mouse or various keyboard keys to interact with the filter dialog. SpreadJS provides the following keyboard keys to interact with the filter dialog: