Getting started with Looker

Best Practices

Documentation

Community

Training

Certification

Articles in this section

See more

filter

Avatar
Looker Support
  • Updated
Follow

Usage

view: view_name {
  filter: filter_name { … }
}

Hierarchy

view
filter

Default Value

None

Accepts

A Looker identifier to name the filter

Special Rules

Filter names may not be shared with any other filter, dimension, or measure within the same view

Definition

The filter parameter declares a filter-only field and a name for that filter. A user can add filter-only fields as filters when exploring, but they cannot add them to their result set. These filter-only fields are made useful via templated filters, which are an advanced LookML topic.

The filter name must:

  • Be unique within any given view
  • Consist of characters a through z (no capital letters), 0 through 9, or _
  • Start with a letter

There are many types of filter fields, as discussed further on the Dimension, filter, and parameter types documentation page.

Examples

Here are some examples for using the filter parameter.

Creating a user-specified filter

Create a filter that lets the user specify the order_region:

filter: order_region { type: string }

Defining a dynamic derived table with a templated filter

As shown on the Templated filters and Liquid parameters documentation page, define a derived table to calculate the lifetime spending for customers in a region that is specified by the user. This example uses the filter created in the previous example as part of a templated filter. The filter input is used in the WHERE clause with Liquid variables:

view: customer_facts { derived_table: { sql: SELECT customer_id, SUM(sale_price) AS lifetime_spend FROM order WHERE {% condition order_region %} order.region {% endcondition %} GROUP BY 1 ;; }   filter: order_region { type: string } }

Using the sql parameter with filter

You can also use the sql parameter with filter, which applies to the SQL WHERE clause whenever the filter has a value. This allows for a dynamic WHERE clause, based on the user filter input.

The following example creates a filter that allows only user names that exist in the dataset:

filter: user_enabled { type: string suggest_dimension: user_name sql: EXISTS (SELECT user_id FROM users WHERE {% condition %} user_name {% endcondition %} and state = 'enabled') ;; }

In the example above, if the complete list of user names in the dataset is “Zach”, “Erin”, and “Brett”, the filter results in the following WHERE clause:

WHERE EXISTS (SELECT user_id FROM users WHERE user_name in ('Zach', 'Erin', 'Brett') and state = 'enabled')

Using filter to define a dynamic derived table and a user-defined filter

Using the earlier example that defines a derived table with a dynamic region value, you can use the sql parameter with a templated filter to dynamically build a WHERE clause that applies to both the derived table and the main Looker-generated query:

view: customer_facts { derived_table: { sql: SELECT customer_id, SUM(sale_price) AS lifetime_spend FROM order WHERE {% condition order_region %} order.region {% endcondition %} GROUP BY 1 ;; } filter: order_region { type: string sql: {% condition order_region %} ${region} {% endcondition %} ;; } dimension: region { type: string sql: ${TABLE}.region ;; }

In the above example, the user provides input to the filter order_region, which in turn provides the value to the region dimension. The region dimension then provides the value of the WHERE clause in the derived table SQL and, because of the sql parameter in the filter definition, the value for the WHERE clause in a Looker-generated query.