Filter enables you to limit a view of your data, based on the rules that you select. This requires a valid combination of data type, operator, and modifying value to define those rules. For example, for the data type Text, you could use the operator Contains, with a modifying value of ‘Blue’ to find all items with the word ‘Blue’ in the name.
Each filter can contain multiple rules, which, in the case of List and Time Period data types, can have multiple values modifying the operators.
When creating filters, use these considerations to help you achieve optimum performance.
|Use a Single Boolean filter on a line item that does not have time or versions applied and does not have a summary.||This enables you to re-use the line item and combine a series of Boolean line items into a single Boolean for use in the filter. This increases speed.|
|Filtering on line items with a line item summary is slow.||A numeric filter on 10,000,000 items can take less than a second, but with a summary will take at least 5 seconds.|
|Filters are heavily impacted when nesting dimensions on rows. The nested filter is slower on the same number of items compared to a flat list.||When testing a 10,000,000 list versus 10 by 1,000,000 nested dimension, the nested dimension filter was 40% slower with the same filter.|
|Multiple filters increase time, especially if any of the preceding filters do not lower the load, they will still take time to evaluate. (so if they don’t often match on anything why have them there).||If filters don't often match on anything, consider removing them.|
|Filter duration affects saved views used in imports.||Check the saved view open time to see the impact on the import performance (adjust the filter if possible).|
|Filter on specific list items||
The operators you use in a filter will vary depending on the data type. Only operators that are valid for a particular data type are available for selection.
Depending on the data type, the operators presented will be a combination of:
- Is equal to
- Is not equal to
- Is greater than
- Is greater than or equal to
- Is less than
- Is less than or equal to
- Is blank
- Is not blank
- Contains (Text only)
- Starts with (Text only)
- Ends with (Text only)
The Filter dialog enables you to find appropriate values using various mechanisms.
When entering values for List and Time Period data types, the displayed drop-down is not case sensitive. You can enter a value in any combination of upper and lower case letters and values are suggested for selection regardless of whether you entered the value in upper or lower case. For example, if February is a valid value in your data, as you type feb, the list displays February for you to select.
For List and Time Period data types, using the operators is equal to and is not equal to, as you type in the Modifier box, all options containing those characters will display in the list. The characters can form any part of the word: for example, if you’re searching on months of the year and enter ber, September, October, November and December will display. Or, if you’re searching on colors, typing the characters ri would return: cerise, apricot, aquamarine, and crimson.
This is useful if you have large data sets and want to select multiple values based on, say, payroll numbers or product codes.
If this to be a saved view, we recommend that you:
Create subsets on lists to show only the desired items;
- Create a new module to show the required data.
This performs faster than the filtered view.
You can select one or more items using the mouse – the values will be entered into the modifier field as you click each one – or you can select single items using the arrow keys; press the Tab key or the Enter key to commit the value.
To remove a value from the modifier field, hover over the name and click the cross.
Click OK to apply your selection. The filtered view will be displayed immediately.
Removing a Filter
If you want to remove a filter, clear the check box next to the Filter icon. Or, open the Filter dialog, click Clear All and then click OK.
Saving a Filtered View
If you’ve created a filtered view of your data that you intend to reuse, save the view.
- Create your filtered view, click the View button then click Save As.
- Enter a name for the filtered view.
- If you want to:
- Open the view on the current page, select the Always open on current page check box.
- Save the filtered view as the default view for the module, select the Save as default view for module check box.
- Click Save. The filtered view will appear on the Contents tab.
- After saving the filtered view, close it. Reopen it to test how long it takes.
Note:After saving a filtered view, test how long it takes to close and re-open it.
The time taken to open the filtered view applies everywhere its used such as other users, imports, and dashboards. If it takes a long time to open the filtered view, you can attempt to optimise the filter performance or reduce the number of filters.
Filter and Data Types
The operators you can choose to filter data vary, depending on the data type. When creating filters, some combinations of data type and operator have conditions applied to the way in which they work. These conditions are outlined below.
Text Data Types
The operators for Text data types provide a range of flexible options with which to filter data. Some operators will accept multiple modifying values and others use text strings. A combination of these options can provide a very powerful filtering mechanism.
The data type Text uniquely uses the operators Contains, Starts with and Ends with.
- Contains searches item names for the characters you specified. If your list contains colors, entering re would return green, red, chartreuse, and ochre.
- Starts with searches the beginning of item names. If your list contains colors, entering bl would return black and blue.
- Ends with searches the end of item names. If your list contains colors, entering ve would return mauve and olive.
List Data Types
For List data types, is equal to and is not equal to can accept multiple values.
Time Period Data Types
Filters for Time Period data types are flexible. You can enter multiple values against the is equal to and is not equal to operators.
Multiple Values in a Modifier
If you enter multiple values into a modifier field for the Time Period data type, using either of the operators is equal to or is not equal to:
...and then change the operator to, say, is greater than, a warning is displayed in the modifier field:
If you click OK, there will be no response. If you change the operator back to is equal to or is not equal to the filter will be applied.
Create a Filter
You can easily build a filter in a dashboard or module:
- Click the Filter icon to, build a filter from scratch.
- Select one or more column headings or line items and open a Filter dialog: the items chosen will be displayed with default values. Further items can be added if required.
- Highlight up to five column headings or line items in the grid and open a Filter dialog. The auto-filter will automatically set up the five criteria with default values. This option is limited to five items — if you select more than five, only the first five will be created and you can add any additional items manually.
- To access a filter dialog, in a dashboard or module view, either:
- Click the Filter icon on the toolbar
- Right-click and select Filter from the menu.
If you haven't selected any rows or columns, a blank Filter dialog will display:
Each tab on the Filter is a dimension in the module.
- To add filter rule, select the dimension tab you want to filter against and click . The Filter dialog adjusts and you can configure a filter rule.
In this example, we first select the line item we want to filter against: Product Month, which is Timeperiod formatted:
Secondly, we select to filter for the products owned by Betty Norris
Lastly, we select the time period values we want to use to filter the view
- Click OK. The filtered view displays.
To disable the filtered view, open the Filter dialog and click Clear next to the Enable filter check box. Or, clear the filter check box on the toolbar:
Auto-filter helps you to set up filter rules quickly. You can select up to five dimensions and auto-filter will create filters for each dimension using default values you can quickly edit.
Auto-filter displays the following default values:
- Numeric: Greater than 0
- Date: equal to today's date
- Boolean: equal to ‘true' (check box marked)
- List: Is equal to …
- Text: Contains…
- None: Blank
You can edit any or all of these values manually.
Create an Auto-filter
Note: Each individual auto-filter has a performance cost. When creating auto-filters consider:
- whether all the filters are required,
- testing each individual filter,
- removing filters that do not change the result,
- whether you can achieve the same criteria with a single line item formula that resolves to a Boolean.
To create a filter:
- Select up to five columns or rows on the grid and click the Filter icon. (Or, on the Data menu, click Filter.
The filter returns rows that meet all criteria, though this can be edited to be any.
- Edit the default values in the fields displayed.
- To add an additional filter, click +.
- To remove a filter, click -.
- Click OK to complete the filter. The filtered data appears.