Dynamic List Filter Options

Modified on Sun, 26 Jul at 4:17 AM

Overview

Excel provides four simple filtering methods for a QQube Dynamic List:

Filter Type	Purpose
Pick Filters	Manually include or exclude specific items.
Text Filters	Filter text fields by conditions such as begins with, contains, or equals.
Value Filters	Filter numeric fields by amount, quantity, rate, or another numeric condition.
Calendar Filters	Filter date fields by standard or relative calendar periods.

Excel also provides an Advanced Filter command for applying multiple criteria and optionally copying the filtered results to another location.

Simple Filter Types

Pick Filters

Purpose: Select one or more specific items from a field list.

Clear items that should not appear, or select only the exact items that should remain in the Dynamic List.

Excel Dynamic List Pick Filter with manually selected items

Text Filters

Purpose: Filter a text field according to its contents.

For example, a Text Filter can display only values that begin with Cover.

Common Text Filter conditions include:

Equals or does not equal.
Begins with or ends with.
Contains or does not contain.

Excel Dynamic List Text Filter options

Value Filters

Purpose: Filter a numeric field according to a numeric condition.

Value Filters can be applied to fields such as:

Quantity.
Amount.
Rate.
Balance.

Excel Dynamic List numeric Value Filter options

Calendar Filters

Purpose: Filter the Dynamic List according to a date or calendar period.

QQube includes calendar fields that provide additional ways to analyze each date.

For example, one date can also be identified by:

Day of the week.
Day number within the year.
Day number within the week.
Month, quarter, and year.

Excel also includes relative date filters similar to the date ranges commonly used in QuickBooks.

Excel Dynamic List Calendar Filter options

Advanced Filtering

Use Excel's Advanced command when the Dynamic List requires multiple conditions.

1. Open Advanced Filter

Select Data > Sort & Filter > Advanced.

2. Define the Criteria

Create a criteria range containing the required field names and conditions.

In this example, the criteria are:

Item Type = Inventory Item
Sales > $2,000

3. Choose the Output

Advanced Filter can:

Filter the Dynamic List in place.
Copy the filtered subset to another location or worksheet.

Excel Advanced Filter using Item Type and Sales criteria

Choose the Appropriate Filter

Filter Type	Use When
Pick Filter	You know the exact items to include or exclude.
Text Filter	You want to filter according to the contents of a text field.
Value Filter	You want to apply a numeric condition to one field.
Calendar Filter	You want to limit the report to a date or relative calendar period.
Advanced Filter	You need multiple conditions or want to copy the filtered records to another location.

Expected Result

Use simple filters for one field or one condition. Use Advanced Filter when the Dynamic List requires multiple criteria or a separate copy of the filtered records.