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.

String

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 a leading -, escape it as ^-. This is only necessary if the - is the leading character; you don't 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.
• To include a comma in a filter expression in LookML, prefix with the escape character, ^. For example:

  field: filtered_count {
      type: count
      filters: [city: "Santa Cruz^, CA"]
    }

Date and Time

Looker date filtering allows for English phrases to be used instead of SQL date functions.

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, 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. See the Relative Dates section for more information.

• {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. Truncating a month or day to a single digit is interpreted as an offset, not a date. For example, 2016-1 is interpreted as 2016 minus one year, or 2015.

These are all the possible combinations of date filters:

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 "January 1st, 2014 and after, 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.

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 following examples, 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.

Seconds

Minutes

Hours

Days

Weeks

Months

Quarters

Years

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.

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. Filters on numbers can also use algebraic interval notation to filter numeric fields.

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.

Supported units of measurement

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

• Meters
• Feet
• Kilometers
• Miles

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

User Attribute Values

To use the value of a user attribute in a filter expression, reference the user attribute with the _user_attributes Liquid variable using the syntax that is required by your database dialect:

{{ _user_attributes['name_of_attribute'] }}

For example, suppose you need to apply an sf_ prefix to the value of the salesforce_username user attribute because that is how the values are stored in your database. To add the prefix to the user attribute value, you can add a matches (advanced) filter on the relevant field and use the _user_attributes Liquid variable in the filter expression as follows:

sf_{{_user_attributes['salesforce_username']}}

You can use the same pattern to insert user attributes into LookML dashboard filters and dashboard element filters.