Defining a Filter
Contents
Introduction
A filter selects data for inclusion in the view. The filter is specified in terms of a condition. Only data that meets the filter condition is included in the view.
Explore Analytics has two kinds of filters:
- Data Filter - This is the most commonly used filter. It selects the data to be used for creating the view. Only data that meets the filter condition is taken and used as input for the report.
- Output Filter - This filter operates on the output of the view and selects which rows or chart data points to keep. Rows or chart data points that don't meet the output filter condition are removed from the view.
Defining a Filter
A filter consists of one or more conditions combined with "and" and "or". The filter restricts the rows to be included in the view to only those rows that meet the filter conditions. For example, in an Employee table, a filter such as "gender = M and job_title = Clerk" will only include table rows where the gender is "M" (M is the data value for a male) and the job title is clerk.
Each condition in the filter consists of a field name, and operator (e.g. the equals operator, "="), and optionally some operands. Let's see a few examples.
In this example, the condition says that the status field has an empty value (it is either NULL or blank). In this example, the operator is "is empty" and no operands.
In this example, the condition says that the customer first name field has a value that is greater or equals to "a" (in dictionary order) and less than or equals to "k". In this example, the operator is "between" and there are two operands.
The list of operators that are available for each field depends on its type.
Date/Time Operators
For a Date/Time field, the following operators are available
- on - the date is on the specified date - any time during the given date
- not on - the date is not on the specified date - any date but the specified date
- during - the date is during the specified period which is relative to the current date. This includes "today", "yesterday", "this week" (starting Sunday), "this month", "this quarter", "this year", "last week", "last month", "last quarter", and "last year"
- not during - the date is not during the specified period (see during)
- before - the date is before (and not including) the specified period
- after - the date is after (and not including) the specified period
- during last - the date/time is during the last specified number of hours/days/weeks/months/years. Here "days" simply mean periods of 24 hours. "weeks" means periods of 7 days, and so on.
- > - the date is later than the specified date
- >= - the date is later than or same as the specified date
- < - the date is earlier than the specified date
- <= - the date is earlier than or same as the specified date
- between - the date is between (and including) the specified dates
- not between - the date is not between (and not including) the specified dates
- is null - the date is NULL (not specified)
- is not null - the date is not NULL (a date value is specified)
Numeric Operators
- = - the number is (numerically) equal to the specified number. Since this is a numerical comparison, there's no difference between "0.10" and ".1", for example
- ≠ - the number is not equal to the specified number
- between - the number is between (and including) the specified numbers
- not between - the number is not between (and not including) the specified numbers
- in - the number is equal to one of the specified numbers
- not in - the number is not equal to any of the specified numbers and is not null
- is null - the number is NULL (not specified). Note that a value of zero is not null
- is not null - the number is not NULL (a date value is specified)
- > - the number is higher than the specified number
- >= - the number is higher than or same as the specified number
- < - the number is lower than the specified number
- <= - the number is lower than or same as the specified number
Text Operators
- = - the text is the same as the specified text (without regard to uppercase/lowercase)
- ≠ - the text is not the same as the specified text
- between - the text is between (and including) the two specified text values (in dictionary order)
- not between - the text is not between (and not including) the specified values
- in - the text is the same as one of the specified values. You can pick values by checking them in the drop-down pick list.
- not in - the text is different from all the specified values and is not null
- is null - the text is NULL (not specified). Note that a value of blank is not null
- is not null - the text is not NULL (a text value is specified)
- is empty - the text is NULL or blank
- is not empty - the text is neither NULL nor blank
- starts with - the text starts with the specified text (without regard to uppercase/lowercase)
- ends with - the text ends with the specified text
- contains - the text contains the specified text
- not contains - the text does not contain the specified text
- > - the text is higher than the specified text (in dictionary order)
- >= - the text is higher than or same as the specified text
- < - the text is lower than the specified text
- <= - the text is lower than or same as the specified text
Lookup
you can filter one table based on a query to another table, using the In Lookup or Not In Lookup filters.
- in lookup - the text is the same as one of the values returned by the lookup query
- not in lookup - the text is different from all the values returned by the lookup query
The following is an example of "in lookup". The dialog defines the query:
- Querying the "selected_products" table. (This table lists the product that we want to include in the report)
- Returning the Product field
- We could add additional filtering of the selected_products by using the filter button
