Excel.PivotValueFilter interface
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
// Link to full sample: https://raw.githubusercontent.com/OfficeDev/office-js-snippets/prod/samples/excel/38-pivottable/pivottable-pivotfilters.yaml
await Excel.run(async (context) => {
// Add a PivotFilter to filter on the values correlated with a row.
// Get the PivotTable.
const pivotTable = context.workbook.worksheets.getActiveWorksheet().pivotTables.getItem("Farm Sales");
// Get the "Farm" field.
const field = pivotTable.hierarchies.getItem("Farm").fields.getItem("Farm");
// Filter to only include rows with more than 500 wholesale crates sold.
const filter: Excel.PivotValueFilter = {
condition: Excel.ValueFilterCondition.greaterThan,
comparator: 500,
value: "Sum of Crates Sold Wholesale"
};
// Apply the value filter to the field.
field.applyFilter({ valueFilter: filter });
await context.sync();
});
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 |
| lower |
The lower-bound of the range for the |
| selection |
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. |
| upper |
The upper-bound of the range for the |
| 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
Remarks
condition
Specifies the condition for the filter, which defines the necessary filtering criteria.
condition: Excel.ValueFilterCondition | "Unknown" | "Equals" | "GreaterThan" | "GreaterThanOrEqualTo" | "LessThan" | "LessThanOrEqualTo" | "Between" | "TopN" | "BottomN";Property Value
Excel.ValueFilterCondition | "Unknown" | "Equals" | "GreaterThan" | "GreaterThanOrEqualTo" | "LessThan" | "LessThanOrEqualTo" | "Between" | "TopN" | "BottomN"
Remarks
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
Remarks
lowerBound
The lower-bound of the range for the between filter condition.
lowerBound?: number;Property Value
number
Remarks
selectionType
Specifies if the filter is for the top/bottom N items, top/bottom N percent, or top/bottom N sum.
selectionType?: Excel.TopBottomSelectionType | "Items" | "Percent" | "Sum";Property Value
Excel.TopBottomSelectionType | "Items" | "Percent" | "Sum"
Remarks
threshold
The "N" threshold number of items, percent, or sum to be filtered for a top/bottom filter condition.
threshold?: number;Property Value
number
Remarks
upperBound
The upper-bound of the range for the between filter condition.
upperBound?: number;Property Value
number
Remarks
value
Name of the chosen "value" in the field by which to filter.
value: string;Property Value
string