Filter expressions
Filter expressions are an advanced way to write filter queries.
You can use filter expressions when you add a filter and choose the matches (advanced) option. For more information, see Adding an advanced match filters.
String
| Example | Description |
|---|---|
| FOO | is equal to "FOO", exactly |
| FOO, BAR | is equal to either "FOO" or "BAR", exactly |
| %FOO% | contains "FOO", matches "buffoon" and "fast food" |
| FOO% | starts with "FOO", matches "foolish" and "food" but not "buffoon" or "fast food" |
| %FOO | ends with "FOO", matches "buffoo" and "fast foo" but not "buffoon" or "fast food" |
| F%OD | starts with an "F" and ends with "OD", matches "fast food" |
| EMPTY | string is empty (has zero characters) or is null (no value) |
| NULL | value is null |
| -FOO | is not equal to "FOO" (is any value except "FOO"), matches "pizza", "trash", "fun" but not "foo" |
| -FOO, -BAR | is not equal to either "FOO" or "BAR", matches any value except "FOO" and "BAR" |
| -%FOO% | doesn't contain "FOO", does not match "buffoon" or "fast food" |
| -FOO% | doesn't start with "FOO", does not match "foolish" or "food" |
| -%FOO | doesn't end with "FOO", does not match "buffoo" or "fast foo" |
| -EMPTY | string is not empty (has at least one character) |
| -NULL | value of column is not null |
| FOO%, BAR | starts with "FOO" or is "BAR" exactly, matches "food" and matches "bar" but not "barfood" |
| FOO%, -FOOD | starts with "FOO" but is not "FOOD" |
| _UF | has any single character followed by "UF", matches "buffoon" |
Note these rules for including special characters in string filters:
- To include ", %, or _, prefix with the escape character, ^. For example: ^", ^%, and ^_
- To include a leading -, escape it as ^-. This is only necessary if the - is the leading character; you do not need to escape - if it is inside the string.
- To include ^, escape it as ^^.
- To include a comma in a
regular UI string filter, prefix the comma with a backslash character, \`. For
example: Santa Cruz\, CA`.
To include a comma with the matches (advanced) option in a filter, prefix the comma with the escape character, ^. For example: Santa Cruz^, CA.
Date and Time
In the following examples, these conventions are used:
- {n} is an integer.
- {interval} is a time
increment, such as hours, days, weeks, or months.
The phrasing you use determines whether the {interval} will include partial time periods or only complete time periods. For example, the expression 3 days includes the current, partial day as well as the prior two days. The expression 3 days ago for 3 days includes the previous three complete days and excludes the current, partial day.
- {time} can specify a time formatted as either YYYY-MM-DD HH:MM:SS or YYYY/MM/DD HH:MM:SS, or a date formatted as either YYYY-MM-DD or YYYY/MM/DD. When using the form YYYY-MM-DD, be sure to include both digits for the month and day, for example, 2016-01.
These are all the possible combinations of date filters:
| Combination | Example | Notes |
|---|---|---|
| this {interval} | this month | You can use this week, this month, this quarter, or this year. Note that this day is not supported. If you want to get data from the current day, you can use today. |
| {n} {interval} | 3 days | |
| {n} {interval} ago | 3 days ago | |
| {n} {interval} ago for {n} {interval} | 3 months ago for 2 days | |
| before {n} {interval} ago | before 3 days ago | |
| before {time} | before 2023-01-01 12:00:00 | before is not inclusive of the time you specify. The expression before 2023-01-01 will return data from all dates before 2023-01-01, but it will not return data from 2023-01-01. |
| after {time} | after 2023-10-05 | after is inclusive of the time you specify. So, the expression after 2023-10-05 will return data from 2023-10-05 and all dates later than 2023-10-05. |
| {time} to {time} | 2023-05-18 12:00:00 to 2023-05-18 14:00:00 | The initial time value is inclusive. The latter time value is not. So the expression 2023-05-18 12:00:00 to 2023-05-18 14:00:00 will return data with the time "2023-05-18 12:00:00" through "2023-05-18 13:59:59". |
| this {interval} to {interval} | this year to second | The beginning of each interval is used. For example, the expression this year to second returns data from the beginning of the year the query is run through to the beginning of the second the query is run. this week to day returns data from the beginning of the week the query is run through to the beginning of the day the query is run. |
| {time} for {n} {interval} | 2023-01-01 12:00:00 for 3 days | |
| today | today | |
| yesterday | yesterday | |
| tomorrow | tomorrow | |
| {day of week} | Monday | Specifying a day of week returns the most recent date that matches the specified day of week. |
| next {week, month, quarter, fiscal quarter, year, fiscal year} | next week | The next keyword is unique in that it requires one of the intervals listed above and will not work with other intervals. |
| {n} {interval} from now | 3 days from now | |
| {n} {interval} from now for {n} {interval} | 3 days from now for 2 weeks |
Absolute dates
| Example | Description |
|---|---|
| 2023/05/29 | sometime on 2023/05/29 |
| 2023/05/10 for 3 days | from 2023/05/10 00:00:00 through 2023/05/12 23:59:59 |
| after 2023/05/10 | 2023/05/10 00:00:00 and after |
| before 2023/05/10 | before 2023/05/10 00:00:00 |
| 2023/05 | within the entire month of 2023/05 |
| 2023/05 for 2 months | within the entire months of 2023/05 and 2023/06 |
| 2023/05/10 05:00 for 5 hours | from 2023/05/10 05:00:00 through 2023/05/10 09:59:59 |
| 2023/05/10 for 5 months | from 2023/05/10 00:00:00 through 2023/10/09 23:59:59 |
| 2023 | entire year of 2023 (2023/01/01 00:00:00 through 2023/12/31 23:59:59) |
| FY2023 | entire fiscal year starting in 2023 |
| FY2023-Q1 | first quarter of the fiscal year starting in 2023 |
Relative dates
Relative date filters allow you to create queries with rolling date values relative to the current date. These are useful when creating queries that update each time you run the query.
For all of the examples below, assume today is Friday, 2023/02/18 18:30:02
Examples for seconds:
| Example | Description |
|---|---|
| 1 second | the current second (2023/05/18 18:30:02) |
| 60 seconds | 60 seconds ago for 60 seconds (2023/05/18 18:29:02 through 2023/05/18 18:30:01) |
| 60 seconds ago for 1 second | 60 seconds ago for 1 second (2023/05/18 18:29:02) |
Examples for minutes:
| Example | Description |
|---|---|
| 1 minute | the current minute (2023/05/18 18:30:00 through 18:30:59) |
| 60 minutes | 60 minutes ago for 60 minutes (2023/05/18 17:31:00 through 2023/05/18 18:30:59) |
| 60 minutes ago for 1 minute | 60 minutes ago for 1 minute (2023/05/18 17:30:00 through 2023/05/18 17:30:59) |
Examples for hours:
| Example | Description |
|---|---|
| 1 hour | the current hour (2023/05/18 18:00 through 2023/05/18 18:59) |
| 24 hours | the same hour of day that was 24 hours ago for 24 hours (2023/05/17 19:00 through 2023/05/18 18:59) |
| 24 hours ago for 1 hour | the same hour of day that was 24 hours ago for 1 hour (2023/05/17 18:00 until 2023/05/17 18:59) |
Examples for days:
| Example | Description |
|---|---|
| today | the current day (2023/05/18 00:00 through 2023/05/18 23:59) |
| 2 days | all of yesterday and today (2023/05/17 00:00 through 2023/05/18 23:59) |
| 1 day ago | just yesterday (2023/05/17 00:00 until 2023/05/17 23:59) |
| 7 days ago for 7 days | the last complete 7 days (2023/05/11 00:00 until 2023/05/17 23:59) |
| today for 7 days | the current day, starting at midnight, for 7 days into the future (2023/05/18 00:00 until 2023/05/24 23:59) |
| last 3 days | 2 days ago through the end of the current day (2023/05/16 00:00 until 2023/05/18 23:59) |
| 7 days from now | 7 days in the future (2023/05/25 00:00 until 2023/05/25 23:59) |
Examples for weeks:
| Example | Description |
|---|---|
| 1 week | top of the current week going forward (2023/05/14 00:00 through 2023/05/20 23:59) |
| this week | top of the current week going forward (2023/05/14 00:00 through 2023/05/20 23:59) |
| before this week | anytime until the top of this week (before 2023/05/14 00:00) |
| after this week | anytime after the top of this week (2023/05/14 00:00 and later) |
| next week | the following Monday going forward 1 week (2023/05/21 00:00 through 2023/05/27 23:59) |
| 2 weeks | a week ago Monday going forward (2023/05/07 00:00 through 2023/05/20 23:59) |
| last week | synonym for "1 week ago" |
| 1 week ago | a week ago Monday going forward 1 week (2023/05/07 00:00 through 2023/05/13 23:59) |
Examples for months:
| Example | Description |
|---|---|
| 1 month | the current month (2023/05/01 00:00 through 2023/05/31 23:59) |
| this month | synonym for "0 months ago" (2023/05/01 00:00 through 2023/05/31 23:59) |
| 2 months | the past two months (2023/04/01 00:00 through 2023/05/31 23:59) |
| last month | all of 2023/04 |
| 2 months ago | all of 2023/03 |
| before 2 months ago | all time before 2023/03/01 |
| next month | all of 2023/06 |
| 2 months from now | all of 2023/07 |
| 6 months from now for 3 months | 2022/11 through 2023/01 |
Examples for quarters:
| Example | Description |
|---|---|
| 1 quarter | the current quarter (2023/04/01 00:00 through 2023/06/30 23:59) |
| this quarter | synonym for "0 quarters ago" (2023/04/01 00:00 through 2023/06/30 23:59) |
| 2 quarters | the past two quarters (2023/01/01 00:00 through 2023/06/30 23:59) |
| last quarter | all of Q1 (2023/01/01 00:00 through 2023/03/31 23:59) |
| 2 quarters ago | all of Q4 of last year (2022/010/01 00:00 through 2022/12/31 23:59) |
| before 2 quarters ago | all time before Q4 of last year |
| next quarter | all of the following quarter (2023/07/01 00:00 through 2023/09/30 23:59) |
| 2023-07-01 for 1 quarter | all of Q3 (2023/07/01 00:00 through 2023/09/30 23:59) |
| 2023-Q4 | all of Q4 (2023/10/01 00:00 through 2023/12/31 23:59) |
Examples for years:
| Example | Description |
|---|---|
| 1 year | all of the current year (2023/01/01 00:00 through 2023/12/31 23:59) |
| this year | all of the current year (2023/01/01 00:00 through 2023/12/31 23:59) |
| next year | all of the following year (2024/01/01 00:00 through 2024/12/31 23:59) |
| 2 years | the past two years (2021/01/01 00:00 through 2023/12/31 23:59) |
| last year | all of 2022 |
| 2 years ago | all of 2021 |
| before 2 years ago | all time before 2021/01/01 (does not include any days between 2021/01/01 and 2021/05/18) |
Examples for boolean values:
| Example | Description |
|---|---|
| yes or Yes | field evaluates to true |
| no or No | field evaluates to false |
| TRUE | field contains true |
| FALSE | field contains false |
Examples for numbers:
Filters on numbers support both natural language expressions (for example, 3 to 10) and relational operators (for example, >20). The OR operator can be used to express multiple filter ranges (for example, 3 to 10 OR 30 to 100). The AND operator can be used to express numeric ranges with relational operators (for example, >=3 AND <=10) to specify a range.
| Example | Description |
|---|---|
| 5 | is exactly 5 |
| NOT 5 <> 5 != 5 | is any value but exactly 5 |
| 1, 3, 5, 7 | is one of the values 1, 3, 5, or 7, exactly |
| NOT 66, 99, 4 | is not one of the values 66, 99, or 4, exactly |
| 1, NOT 2 | is neither 1 nor 2 |
| 1, NOT 2, > 100 | is neither 1, nor 2, nor greater than 100 |
| 5, NOT 6, NOT 7 | is 5, is not 6 or 7 |
| 5.5 to 10 >= 5.5 AND <= 10 | is 5.5 or greater but also 10 or less |
| NOT 3 to 80.44 < 3 OR > 80.44 | is less than 3 or greater than 80.44 |
| 1 to >= 1 | is 1 or greater |
| to 10 <= 10 | is 10 or less |
| > 10 AND <= 20 OR 90 | is greater than 10 and less than or equal to 20, or is 90 exactly |
| >= 50 AND <= 100 OR >= 500 AND <= 1000 | is between 50 and 100, inclusive, or between 500 and 1000, inclusive |
| NULL | has no data in it |
| NOT NULL | has some data in it |
| (1, 7) | interpreted as 1 < x < 7 where the endpoints are not included |
| [5, 90] | interpreted as 5 <= x <= 90 where the endpoints are included |
| (12, 20] | interpreted as 12 < x <= 20 where 12 is not included, but 20 is included |
| [12, 20) | interpreted as 12 <= x < 20 where 12 is included, but 20 is not included |
| (500, inf) | interpreted as x > 500 where 500 is not included and infinity is always expressed as being "open" (not included) |
| (-inf, 10] | interpreted as x <= 10 where 10 is included and infinity is always expressed as being "open" (not included) |
| [0, 9], [20,29] | the numbers between 0 and 9 inclusive or 20 to 29 inclusive |
| [0, 10], 20 | 0 to 10 inclusive or 20 |
| NOT (3, 12) | interpreted as x < 3 and x > 12 |
| NOT 1o, [1, 5) | all numbers except 10, and except 1 up to but not including 5 |