Looker Filter Expressions

On this Page
Docs Menu

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

Example Description
FOO is equal to ‘FOO’, exactly
FOO,BAR is equal to either ‘FOO’ OR ‘BAR’, exactly
%FOO% contains ‘FOO’, matches ‘Bufoon’ and ‘Thai Food’
FOO% starts with ‘FOO’, matches ‘Foolish’ and ‘food’
%FOO ends with ‘FOO’, matches ‘Bufoo’ and ‘ThaiFoo’
M%ND starts with an ‘M’ and ends with ‘ND’, matches ‘Magic Wand’
EMPTY string is empty/blank (has zero characters) or is NULL
NULL value is NULL (please remember to put NULL in quotes, as described in the filters documentation)
-FOO is not equal to ‘FOO’. Can also be read as “is any value BUT ‘FOO’”. Matches ‘pizza’, ‘trash’, ‘fun’ but not ‘foo’
-FOO,-BAR is not equal to neither ‘FOO’ nor ‘BAR’. Matches any value except ‘FOO’ and ‘BAR’
-%FOO% doesn’t contain ‘FOO’, does not match ‘Bufoon’ or ‘Thai Food’
-FOO% doesn’t start with ‘FOO’, does not match ‘Foolish’ or ‘food’
-%FOO doesn’t end with ‘FOO’, does not match ‘Bufoo’ or ‘ThaiFoo’
-EMPTY string is not empty/blank (has more than zero characters)
-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’
_AB has any single character followed by ‘AB’, Matches ‘CAB’ and ‘_AB’ for example

Including Special Characters in String Filters

Note these rules for including special characters in string filters:

  • To include % or _, prefix with the escape character, ^. For example: ^% and ^_
  • To include ^, escape it as ^^.
  • To include an item with commas, wrap it in double-quotes. For example: "Santa Cruz, CA", "San Jose, CA"

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 Wednesday, 2013/12/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 …

  • {n} is an integer
  • {interval} is a time increment such as hours, days, weeks, months, etc
  • {time} is a time of the form YYYY-MM-DD HH:MM:SS or a date of the form YYYY-MM-DD

These are all the possible combinations of date filters:

  • this {interval}
  • {n} {interval}
  • {n} {interval} ago
  • {n} {interval} ago for {n} {interval}
  • before {n} {interval} ago
  • before {time}
  • after {time}
  • {time} to {time}
  • {time} for {n} {interval}
  • today
  • yesterday
  • tomorrow
  • next {interval}

Date filters can also be combined together:

  • To get OR logic: Type multiple conditions into the same filter, separated by commas. For example today, 7 days ago means “today or 7 days ago”.
  • To get AND logic: Type your conditions, one by one, into multiple date or time filters. For example you could put after 2014-01-01 into a Created Date filter, then put before 2 days ago into a Created Time filter. This would mean “after January 1st, 2014 and before 2 days ago”.

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
2013/01/29 sometime on 2013/01/29
2013/01/10 for 3 days between 2013/01/10 and 2013/01/13
2013/01/10 for 1 day between 2013/01/10 00:00:00 and 2013/01/11 23:59:59
after 2013/01/10 after 2013/01/10 00:00:00
before 2013/01/10 before 2013/01/10 00:00:00
2013/01 within the entire month of 2013/01
2013/01 for 2 months within the entire months of 2013/01 and 2013/02
2013/01/10 05:00 for 5 hours between 2013/01/10 05:00:00 and 2013/01/10 10:00:00
2013/01/10 for 5 months between 2013/01/10 and 2013/06/10
2013 entire year of 2013 (2013/01/01 00:00 until 2013/12/31 23:59)
FY2017 entire fiscal year of 2017 (if your Looker developers have specified that your fiscal year starts in April then this is 2017/04/01 00:00 until 2018/03/31 23:59)
FY2017-Q1 first quarter of fiscal year of 2017 (if your Looker developers have specified that your fiscal year starts in April then this is 2017/04/01 00:00 until 2017/07/31 23: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 ADDED3.54

Example Description
second the current second (2013/12/18 18:00:02 until now)
60 seconds 60 seconds ago until now (2013/12/18 17:59:02 until now)
60 seconds ago for 1 second 60 seconds ago for 1 second (2013/12/18 17:59:02 until 2013/12/18 17:59:03)

Minutes

Example Description
minute the current minute (2013/12/18 18:00 until now)
60 minutes 60 minutes ago until now (2013/12/18 17:00 until now)
60 minutes ago for 1 minute 60 minutes ago for 1 minute (2013/12/18 17:00 until 2011/12/18 17:01)

Hours

Example Description
hour the current hour (2013/12/18 18:00 until now)
24 hours the same hour of day that was 24 hours ago until now (2013/12/17 18:00 until now)
24 hours ago for 1 hour the same hour of day that was 24 hours ago for 1 hour (2013/12/17 18:00 until 2011/12/17 19:00)

Days

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

Weeks

Example Description
1 week top of the current week going forward (2013/12/16 00:00 until now)
this week top of the current week going forward (2013/12/16 00:00 until 2013/12/23 00:00)
before this week anytime until the top of this week (before 2013/12/16 00:00)
after this week anytime after the top of this week (after 2013/12/16 00:00)
2 weeks a week ago Monday going forward (2013/12/09 00:00 until now)
last week synonym for ‘1 week ago’
1 week ago a week ago Monday going forward 1 week (2013/12/09 00:00 until 2013/12/16 00:00)

Months

Example Description
1 month the current month (2013/12/01 00:00 until 2014/01/01 00:00)
this month synonym for ‘0 months ago’ (2013/12/01 00:00 until 2014/01/01 00:00)
2 months the past two months (2013/11/01 00:00 until now)
last month all of 2013/11
2 months ago all of 2013/10
before 2 months ago all time before 2013/10/01

Quarters

Example Description
1 quarter top of the current quarter (Q4), going forward until now
this quarter all of the current quarter (2013/10/01 00:00 until 2013/12/31 23:59)
2 quarters the past two quarters (2013/07/01 00:00 until 2013/12/31 23:59)
last quarter all of Q3 (2013/07/01 00:00 until 2013/09/30 23:59)
2 quarters ago all of Q2 (2013/04/01 00:00 until 2013/06/30 23:59)
before 2 quarters ago all time before Q2
2013-10-01 for 1 quarter all of Q4 (2013/10/01 00:00 until 2013/12/31 23:59)
2013-Q4 all of Q4 (2013/10/01 00:00 until 2013/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 (2013/01/01 00:00 until 2013/12/31 23:59)
this year all of the current year (2013/01/01 00:00 until 2013/12/31 23:59)
2 years the past two years (2012/01/01 00:00 until 2013/12/31 23:59)
last year all of 2012
2 years ago all of 2011
before 2 years ago all time before 2011/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 field evalutes to true (must be lower case, only for use with type: yesno dimensions)
no field evalutes to false (must be lower case, only for use with type: yesno dimensions)
TRUE field contains true (only for use with fields that contain boolean database values)
FALSE field contains false (only for use with fields that contain boolean database values)

Number

Filters on numeric types 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 realtional operators (for example >=3 AND <=10) relational operators 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 longitude
-NULL (works the same as NOT NULL) location has both a non null latitude and 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:

  • meters
  • feet
  • kilometers
  • miles

Please note that singular units of measurement (e.g. “mile”) are not supported. For example, filtering for a one mile radius should be written “within 1 miles of 36.97, -122.03”.

Interval Notation

Numeric filters 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
Still have questions?
Go to Discourse - or - Email Support
Top