Advanced filtering allows the user to construct more complex rules for filtering. As well as simple comparison of values, advanced filtering supports compound filtering options where multiple comparisons can be evaluated individually or in groups.
For those familiar with SQL, this is equivalent to compound WHERE statements such as
WHERE (SALES > 300000 AND MARGIN < 0.25) OR (SALES < 300000 AND MARGIN > 0.3).
Any number of conditions may be combined together in any combination of individual and compound statements
The individual filter conditions are specified using the other filter wizard panels as described in the Value Filtering topic.
Operations
Additional filters are added by clicking on the "+" icon when hovering over an existing filter:
To specify how these two filters should be combined, click on the small drop down menu between the two conditions.
Multi-filter Switches
Three options are available, with their function illustrated by the icon and text:
-
AND - Both conditions must be met to return values. Equivalent to the intersection of two or more sets.
-
OR - Either condition must be met to return values. Equivalent to a union of two or more sets.
-
NESTED - the outer filter is filtered further by the inner one(s). In effect the filters are applied on the results of previous ones.
Grouping filter operations and switches
Note. When setting the relationship between two compound conditions, only AND and OR are available.
To group multiple conditions together, select the conditions by clicking the selection box. The "Create Group" button becomes active. click on this button to group the conditions together.
Grouped conditions are identified by the visual bracket across the conditions.
To ungroup the conditions, click on the bracket and select "Ungroup"
Example
Advanced filtering walk through.
To better understand the setup and operation of advanced filters it is best to walk through an example. Let's take the SQL WHERE clause discussed in the introduction.
We want to determine the Manufacturers and Occupations that have Sales greater than 300,000 and Margin less than 0.25 or Sales less than 300,000 and Margin greater than 0.3
We can set our initial grid thus
Click on the Filter Wizard button in the Query Ribbon.
Select Manufacturer and Occupation as the filter targets and set the first filter to Filter Above, the value to 300000 and set the Measure to be Sales.
Add another filter by clicking on the "+" icon when hovering over the first filter as described above.
Set this filter to be below 0.25 for the Measure Margin:
Set the relationship between these conditions to be AND, using the drop down menu between the two conditions.
Now group these conditions together by selecting them via their checkboxes and clicking on "Create Group":
Resulting in
Add additional conditions in a similar way for Sales less than 300,000 and Margin greater than 0.3. Group them together to create a second group.
We now need to define how these two groups interact by selecting the OR option between them:
Finally, apply the filter by clicking on the "Apply" button to filter our data:
We can identify those combination of Manufacturer and occupation that have Sales greater than 300,000 and Margin less than 0.25 or Sales less than 300,000 and Margin over 0.3:
Of course, the Measures used for filtering do not need to be present in the grid, but they are included here to see the effect of advanced filtering in action.