User Guide Getting Started Help Center Documentation Community Training
Looker Filter Expressions

Filter expressions are an advanced way to filter Looker queries, and this page describes how to write them. In the Explore section of Looker you can use them by adding a filter and choosing the matches (advanced) option. They are also used in LookML for elements that take a filter parameter.

When using filter expressions in LookML, you should quote the expression (see the filters documentation for proper use). This is especially important for logical values like NULL.

String

Matches in string filters depend on the case_sensitive setting in your model file, and on whether your dialect supports case sensitivity. For example, if case_sensitive is enabled in your model, the expression FOO will not match the word “food”. If case_sensitive is not enabled, or if your dialect does not support case sensitivity, the expression FOO will match the word “food”.

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 (please remember to put NULL in quotes, as described in the filters documentation)
-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 (please remember to put -NULL in quotes, as described in the filters documentation)
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”

Including Special Characters in String Filters

Note these rules for including special characters in string filters:

Date & Time

Looker date filtering allows for English phrases to be used instead of SQL date functions. For all the examples below assume today is Friday, 2018/05/18 18:30:02. In Looker, weeks start on Monday unless you change that setting with week_start_day.

Basic Structure of Date and Time Filters

For the following examples …

These are all the possible combinations of date filters:

Combination Example Notes
this {interval} this day
{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 2018-01-01 12:00:00
after {time} after 2018-10-05 after is inclusive of the time you specify. So, the expression after 2018-10-05 will return data from 2018-10-05 and all dates later than 2018-10-05.
{time} to {time} 2018-05-18 12:00:00 to
2018-05-18 14:00:00
The initial time value is inclusive. The latter time value is not. So the expression 2018-05-18 12:00:00 to 2018-05-18 14:00:00 will return data with the time “2018-05-18 12:00:00” through “2018-05-18 13:59:59”.
{time} for {n} {interval} 2018-01-01 12:00:00 for 3 days
today today
yesterday yesterday
tomorrow tomorrow
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

Date filters can also be combined together:

Absolute Dates

Absolute date filters use the specific date values to generate query results. These are useful when creating queries for specific date ranges.

Example Description
2018/05/29 sometime on 2018/05/29
2018/05/10 for 3 days from 2018/05/10 00:00:00 through 2018/05/12 23:59:59
after 2018/05/10 after 2018/05/10 23:59:59
before 2018/05/10 before 2018/05/10 00:00:00
2018/05 within the entire month of 2018/05
2018/05 for 2 months within the entire months of 2018/05 and 2018/06
2018/05/10 05:00 for 5 hours from 2018/05/10 05:00:00 through 2018/05/10 09:59:59
2018/05/10 for 5 months from 2018/05/10 00:00:00 through 2018/10/09 23:59:59
2018 entire year of 2018 (2018/01/01 00:00:00 through 2018/12/31 23:59:59)
FY2018 entire fiscal year starting in 2018 (if your Looker developers have specified that your fiscal year starts in April then this is 2018/04/01 00:00 through 2019/03/31 23:59)
FY2018-Q1 first quarter of the fiscal year starting in 2018 (if your Looker developers have specified that your fiscal year starts in April then this is 2018/04/01 00:00:00 through 2018/06/30 23:59:59)

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.

Seconds

Example Description
1 second the current second (2018/05/18 18:30:02)
60 seconds 60 seconds ago for 60 seconds (2018/05/18 18:29:02 through 2018/05/18 18:30:01)
60 seconds ago for 1 second 60 seconds ago for 1 second (2018/05/18 18:29:02)

Minutes

Example Description
1 minute the current minute (2018/05/18 18:30:00 through 18:30:59)
60 minutes 60 minutes ago for 60 minutes (2018/05/18 17:30:00 through 2018/05/18 18:29:59)
60 minutes ago for 1 minute 60 minutes ago for 1 minute (2018/05/18 17:30:00 through 2018/05/18 17:30:59)

Hours

Example Description
1 hour the current hour (2018/05/18 18:00 through 2018/05/18 18:59)
24 hours the same hour of day that was 24 hours ago for 24 hours (2018/05/17 18:00 through 2018/05/18 17:59)
24 hours ago for 1 hour the same hour of day that was 24 hours ago for 1 hour (2018/05/17 18:00 until 2018/05/17 18:59)

Days

Example Description
today the current day (2018/05/18 00:00 through 2018/05/18 23:59)
2 days all of yesterday and today (2018/05/17 00:00 through 2018/05/18 23:59)
1 day ago just yesterday (2018/05/17 00:00 until 2018/05/17 23:59)
7 days ago for 7 days the last complete 7 days (2018/05/11 00:00 until 2018/05/17 23:59)
today for 7 days the current day, starting at midnight, for 7 days into the future (2018/05/18 00:00 until 2018/05/24 23:59)
last 3 days 2 days ago through the end of the current day (2018/05/16 00:00 until 2018/05/18 23:59)
7 days from now 7 days in the future (2018/05/25 00:00 until 2018/05/25 23:59)

Weeks

Example Description
1 week top of the current week going forward (2018/05/14 00:00 through 2018/05/20 23:59)
this week top of the current week going forward (2018/05/14 00:00 through 2018/05/20 23:59)
before this week anytime until the top of this week (before 2018/05/14 00:00)
after this week anytime after the top of this week (2018/05/14 00:00 and later)
next week the following Monday going forward 1 week (2018/05/21 00:00 through 2018/05/27 23:59)
2 weeks a week ago Monday going forward (2018/05/07 00:00 through 2018/05/20 23:59)
last week synonym for “1 week ago”
1 week ago a week ago Monday going forward 1 week (2018/05/07 00:00 through 2018/05/13 23:59)

Months

Example Description
1 month the current month (2018/05/01 00:00 through 2018/05/31 23:59)
this month synonym for “0 months ago” (2018/05/01 00:00 through 2018/05/31 23:59)
2 months the past two months (2018/04/01 00:00 through 2018/05/31 23:59)
last month all of 2018/04
2 months ago all of 2018/03
before 2 months ago all time before 2018/03/01
next month all of 2018/06
2 months from now all of 2018/07
6 months from now for 3 months 2018/11 through 2019/01

Quarters

Example Description
1 quarter the current quarter (2018/04/01 00:00 through 2018/06/30 23:59)
this quarter synonym for “0 quarters ago” (2018/04/01 00:00 through 2018/06/30 23:59)
2 quarters the past two quarters (2018/01/01 00:00 through 2018/06/30 23:59)
last quarter all of Q1 (2018/01/01 00:00 through 2018/03/31 23:59)
2 quarters ago all of Q4 of last year (2017/010/01 00:00 through 2017/12/31 23:59)
before 2 quarters ago all time before Q4 of last year
next quarter all of the following quarter (2018/07/01 00:00 through 2018/09/30 23:59)
2018-07-01 for 1 quarter all of Q3 (2018/07/01 00:00 through 2018/09/30 23:59)
2018-Q4 all of Q4 (2018/10/01 00:00 through 2018/12/31 23:59)

If your Looker developers have specified using a fiscal year then you can type fiscal in these expressions to use a fiscal quarter instead of a calendar quarter. For example, you can use last fiscal quarter.

Years

Example Description
1 year all of the current year (2018/01/01 00:00 through 2018/12/31 23:59)
this year all of the current year (2018/01/01 00:00 through 2018/12/31 23:59)
next year all of the following year (2019/01/01 00:00 through 2019/12/31 23:59)
2 years the past two years (2017/01/01 00:00 through 2018/12/31 23:59)
last year all of 2017
2 years ago all of 2016
before 2 years ago all time before 2016/01/01

If your Looker developers have specified using a fiscal year then you can type fiscal in these expressions to use a fiscal year instead of a calendar quarter. For example, you can use last fiscal year.

Boolean

Filtering on true or false type values in Looker requires you to know what type of true or false value you’re interacting with.

Example Description
yes or Yes field evaluates to true

Looker developers: for type: yesno dimensions use lowercase, for filters parameters (like those used in a measure or used in an always_filter) use uppercase
no or No field evaluates to false

Looker developers: for type: yesno dimensions use lowercase, for filters parameters (like those used in a measure or used in an always_filter) use uppercase
TRUE field contains true (for fields that contain Boolean database values)
FALSE field contains false (for fields that contain Boolean database values)

Number

Filters on numbers support both natural language expressions (for example 3 to 10) and relational operators (for example >20). Looker supports the OR operator 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
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 (please remember to put NULL in quotes, as described in the filters documentation)
NOT NULL has some data in it (please remember to put NOT NULL in quotes, as described in the filters documentation)

Location

Location filter expressions are based on latitude and longitude, but can accept some natural language to define boxes and circles within which to limit a search.

Example Description
36.97, -122.03 location is exactly at latitude 36.97, longitude 122.03
40 miles from 36.97, -122.03 location is within 40 miles of latitude 36.97, longitude -122.03
inside box from 72.33, -173.14 to 14.39, -61.70 location is within a box whose northwest corner is at latitude 72.33, longitude -173.14, and whose southeast corner is at latitude 14.39, longitude -61.70
NOT NULL (works the same as -NULL) location has both a non-null latitude and a non-null longitude
-NULL (works the same as NOT NULL) location has both a non-null latitude and a non-null longitude
NULL location has a null latitude, or a null longitude, or both are null

Supported Units of Measurement

To filter in an area around a certain location, you can use these units:

Singular units of measurement are not supported. For example, filtering for a one mile radius should be written within 1 miles of 36.97, -122.03.

Interval Notation

Filters on numbers can also use algebraic interval notation to filter numeric fields.

Open Interval

Example Description
(a, b) interpreted as a < x < b where the endpoints are NOT included

While this notation resembles an ordered pair, in this context it refers to the interval upon which you are working.

Closed Interval

Example Description
[a, b] interpreted as a <= x <= b where the endpoints are included

Half-open Interval

Example Description
(a, b] interpreted as a < x <= b where a is not included, but b is included
[a, b) interpreted as a <= x < b where a is included, but b is not included

Non-ending Interval

Example Description
(a, inf) interpreted as x > a where a is not included and infinity is always expressed as being “open” (not included)
(-inf, a] interpreted as x <= b where b is included and again, infinity is always expressed as being “open” (not included)

Note: inf may be omitted and the above may be written as (a,) or (,a]

Multiple Intervals

The union of multiple intervals may be expressed with a comma. For example:

Example Description
[0,9],[20,29] the numbers between 0 and 9 inclusive or 20 to 29 inclusive

Intervals and Numbers at the Same Time

Example Description
[0,10],20 0 to 10 inclusive or 20

Using NOT

Example Description
NOT 10,[1,5) all numbers except 10, and except 1 up to but not including 5
Top