Search Knowledgebase

Follow

List of operators used in filtering ad hoc reports

Below is a list of the available filter operators and examples of each. These are available when building an ad hoc report query. This information was taken from the WebIntelligence documentation which can be found within the WebI help screen. 

 

Equal to

Use the Equal to operator to obtain data equal to a value.

For example, to return data for the US only, create the filter "County Equal to US".

Not Equal to

Use the Not Equal to operator to obtain data not equal to a value.

For example, to return data for all countries except the US create the filter "County Not Equal to US".

Greater than

Use the Greater than operator to retrieve data greater than a value.

For example, to retrieve data for customers aged over 60, create the filter "[Customer Age] Greater than 60".

Greater than or Equal to

Use the Greater than or equal to operator to retrieve data greater than or equal to a value.

For example, to retrieve data for revenue starting from $1.5M, create the filter "[Revenue] Greater than or equal to 1500000".

Less than

Use the Less than operator to retrieve data lower than a value.

For example, to retrieve data for exam grades lower than 40, create the filter "[Exam Grade] Less than 40".

Less than or Equal to

Use the Less than or equal to operator to retrieve data less than or equal to a value.

For example, to retrieve data for customers whose age is 30 or less, create the filter "[Age] Less than or equal to 30".

Between

Use the Between operator to retrieve data between two boundary values including the two boundary values.

The first value declared must be lower than the second value.

For example, to retrieve data for weeks starting at week 25 and finishing at 36 (including week 25 and week 36), create the filter "[Week] Between 25 and 36".

Not between

Use the Not between operator to retrieve data outside the range of two values.

For example, to retrieve data for all the weeks of the year, except for and not including weeks 25 through 36, create the filter "[Week] Not between 25 and 36".

In list

Use the In list operator to retrieve data corresponding to values in a list of values.

For example, to retrieve data only for the US, UK and Japan, create the filter [Country] In list, when you can type values in the Type a value field, you enter US;UK;Japan.

When used in a query filter with a hierarchical list of values, either from a dimension associated with a hierarchical list of values or a hierarchy object, In list allows the selection of multiple members from any levels of the hierarchy. For example, a prompt on the [Geography] hierarchy using the In list operator allows the selection of [Paris] at the City level and [Canada] at the Country level in the prompt.

When used in a report filter, In list produces a flat list of values.

Not In List

Use the Not in list operator to retrieve data that does not correspond to multiple values.

For example, if you do not want to retrieve data for the US, UK and Japan, create the filter "[Country] Not in list". In the Type a value field, you enter US;UK;Japan.

When used with a hierarchical list of values, either from a dimension associated with a hierarchical list of values, a hierarchy object or a level object, Not in list allows the selection of multiple members from any levels of the hierarchy. For example, a prompt on the [Geography] hierarchy using the Not in list operator allows selection of [Paris] at the City level and [Canada] at the Country level in the prompt.

Matches Pattern

Use the Matches pattern operator to retrieve data that includes a specific string or part of a string.

For example, to retrieve customers whose date of birth is 1972, create the filter [DOB] Matches pattern "72".

Different From Pattern

Use the Different from pattern operator to return data that doesn't include a specific string.

For example, to retrieve customers whose date of birth is not 1972, create the filter [DOB] Different from pattern '72'.

Both

Use the Both operator to retrieve data that corresponds to two values.

For example, to retrieve customers who have both fixed and mobile telephone numbers, create the filter [Account Type] Both 'Fixed' And 'Mobile'.

Except

Use the Except operator to retrieve data that corresponds to one value and excludes another.

For example, to retrieve customers who have only a fixed telephone number and no mobile telephone number, create the filter [Account Type] 'Fixed' Except 'Mobile'.

The Except operator is more restrictive than Different from or Not in list. For example, a report that returns customers and that includes the filter [Lines] Different From 'Accessories' excludes all sales records where the item sold is part of the 'Accessories' line. If the same customer has purchased Accessories and non-Accessories items, the customer still appears in the report, but their spending total includes only non-Accessories sales.

If the filter is [Lines] Except 'Accessories', only customers who have bought no accessories are included in the report.

Was this article helpful?
1 out of 1 found this helpful
Have more questions? Submit a request