ExcelScript.PivotValueFilter interface

Package:

ExcelScript

Configurable template for a value filter to apply to a PivotField. The condition defines what criteria need to be set in order for the filter to operate.

Remarks

Examples

/**
 * This script applies a PivotValueFilter to the first row hierarchy in the PivotTable.
 */
function main(workbook: ExcelScript.Workbook) {
  // Get the PivotTable on the current worksheet.
  let sheet = workbook.getActiveWorksheet();
  let pivotTable = sheet.getPivotTables()[0];

  // Get the first row hierarchy to use as the field which gets filtered.
  let rowHierarchy = pivotTable.getRowHierarchies()[0];

  // Get the first data hierarchy to use as the values for filtering the rows.
  let dataHierarchy = pivotTable.getDataHierarchies()[0];

  // Create a filter that excludes values greater than 500.
  let filter: ExcelScript.PivotValueFilter = {
    condition: ExcelScript.ValueFilterCondition.greaterThan,
    comparator: 500,
    value: dataHierarchy.getName()
  };

  // Apply the filter.
  rowHierarchy.getPivotField(rowHierarchy.getName()).applyFilter({
    valueFilter: filter
  });
}

Properties

comparator	The comparator is the static value to which other values are compared. The type of comparison is defined by the condition. For example, if comparator is "50" and condition is "greaterThan", all item values that are not greater than 50 will be removed by the filter.
condition	Specifies the condition for the filter, which defines the necessary filtering criteria.
exclusive	If true, filter excludes items that meet criteria. The default is false (filter to include items that meet criteria).
lowerBound	The lower-bound of the range for the between filter condition.
selectionType	Specifies if the filter is for the top/bottom N items, top/bottom N percent, or top/bottom N sum.
threshold	The "N" threshold number of items, percent, or sum to be filtered for a top/bottom filter condition.
upperBound	The upper-bound of the range for the between filter condition.
value	Name of the chosen "value" in the field by which to filter.

Property Details

comparator

The comparator is the static value to which other values are compared. The type of comparison is defined by the condition. For example, if comparator is "50" and condition is "greaterThan", all item values that are not greater than 50 will be removed by the filter.

comparator?: number;

Property Value

number

condition

Specifies the condition for the filter, which defines the necessary filtering criteria.

condition: ValueFilterCondition;

Property Value

ExcelScript.ValueFilterCondition

exclusive

If true, filter excludes items that meet criteria. The default is false (filter to include items that meet criteria).

exclusive?: boolean;

Property Value

boolean

lowerBound

The lower-bound of the range for the between filter condition.

lowerBound?: number;

Property Value

number

selectionType

Specifies if the filter is for the top/bottom N items, top/bottom N percent, or top/bottom N sum.

selectionType?: TopBottomSelectionType;

Property Value

ExcelScript.TopBottomSelectionType

threshold

The "N" threshold number of items, percent, or sum to be filtered for a top/bottom filter condition.

threshold?: number;

Property Value

number

upperBound

The upper-bound of the range for the between filter condition.

upperBound?: number;

Property Value

number

value

Name of the chosen "value" in the field by which to filter.

value: string;

Property Value

string