New LookML
Old LookML
New LookML
Additional LookML Basics

This page covers more advanced aspects of programming LookML.

Labeling Fields (and Names in the UI)

Looker converts LookML field names into strings displayed in the UI by combining the view name and the field’s short name. For example, Orders Amount. On this page, the view name is capitalized (ORDERS Amount) to make the discussion clearer.

If you would like a field to be named differently than its column name in a table, simply change the field name and declare its sql: linkage. In the example below, there is a table ‘airports’ with a column ‘cntrl_twr’. Looker’s Generator would generate the following declaration:

view: airports { dimension: cntrl_twr { # full name: airports.cntrl_twr type: yesno # default name: AIRPORT Cntrl Twr (Yes/No) sql: ${TABLE}.cntrl_twr ;; # the sql expression for this field } }

We will rename the ‘cntrl_twr’ dimension to be human readable.

view: airports { dimension: has_control_tower { # full name: airports.has_control_tower type: yesno # aliased name: AIRPORTS Has Control Tower (Yes/No) sql: ${TABLE}.cntrl_twr ;; # the sql expression for this field } }

Filtering Counts by a Dimension

It is pretty easy to group by a dimension and count entities—grouping by USERS Country, ORDERS Count, will tell you where your orders are coming from by country. However, it is often useful to build a count filtered by some dimensional value. For example, you could make a new measure ORDERS Us Count:

view: users { dimension: country {} } view: orders { dimension: id { primary_key: yes sql: ${TABLE}.id ;; } measure: count { type: count drill_fields: [detail] } measure: us_count { type: count # COUNT(CASE WHEN = ‘US’ THEN 1 ELSE NULL END) drill_fields: [detail] # Also, when drilling, adds the filter’US’ filters: { field: value: "US" } } }

Filters can use any expression. If you wanted a field that counted users from the EU, you could use something like this:

measure: eu_count { type: count # COUNT(CASE WHEN IN ‘UK’,’FR’,’ES’ THEN 1 ELSE NULL END) drill_fields: [detail] filters: { field: value: "UK,FR,ES" } }

If you want to filter with a mathematical expression, be sure to enclose it in double quotes:

measure: total_orders_above_100_dollars { type: sum # SUM(CASE WHEN order.value > 100 THEN order.value ELSE NULL END) sql: ${order.value} ;; drill_fields: [detail] filters: { field: order.value value: ">100" } }


Many key performance indicators (KPI) are expressed in the form of percentages, such as “the Percent of items returned”, “the percent of emails that resulted in a sale”, or other instances of “the percent of X that Y”. In LookML, the design pattern is to create counts for the two conditions and create a third field that computes the percentage between the two.

dimension: returned { type: yesno } measure: count { # total count of items type: count_distinct sql: ${TABLE}.id ;; drill_fields: [detail] } measure: returned_count { # count of returned items type: count_distinct sql: ${TABLE}.id ;; drill_fields: [detail] filters: { field: returned value: "Yes" } } measure: percent_returned { type: number sql: 100.0 * ${returned_count} / NULLIF(${count}, 0) ;; value_format: "0.00" }

NOTE: Use the form below when computing percentages. In Postgres, counts are integers and division between integers results in integers. Multiplying by 100.0 converts the first count to a floating point number, thus converting the rest of the expression to a float. In order to avoid divide by zero errors, the NULLIF(value, 0) will convert a zero value to null, making the result null and avoiding an error.

100.0 * ${returned_count} / NULLIF(${count}, 0)

Using Sets for Drill-Down Details

One of the most powerful features of Looker is the ability to drill into data to see the underlying entities that make up a count or other measure.

When a measure is clicked on the Looker UI, a new query is created localizing the set of data making up the measure. Each value for each dimension on the row in the table gets added to the current filters.

In order to show the detail, Looker needs a specified list of drill fields to show when the measure’s value has been clicked. When you generate a model, the generator typically creates some initial drill fields for you. In addition, you can add drill fields yourself. For example, assume that we are measuring ORDERS Count by USERS State in the last week. In Looker, the query would look something like this:


If we click on the 24 in the California row, the expected behavior is that we’d see the 24 orders that came from California.

Looker handles adding the filter USERS State: California, but Looker doesn’t know which fields you want to show in the order, you need to declare that in your model. We’ll need a set for that.

In LookML a set is a list of field names (dimension, measure and filter names). Sets are used to tell Looker which fields:

The same set can be used in many places in a model so looker provides several ways of creating sets.

Literal Sets

The simplest form of a set is a literal set. A literal created by simply declaring the set as an array. You can declare literal sets by using ‘[]’.

Given this example:

view: customers { dimension: id { primary_key: yes } measure: count { type: count } dimension: city {} dimension: state {} dimension: name {} }

The fields we’d like to show are id, name, and city.

In the measure, we could simply declare a literal array.

measure: count { type: count drill_fields: [id, name, city] }

In old LookML, based on YAML, you can also use a dash (-) to define the same array, as shown here:

- measure: count type: count drill_fields: - id - name - city

For sets that are only used once, declaring them literally is simple and easily understood.

Named Sets

Suppose we have two counts; CUSTOMERS Count and CUSTOMERS In California Count. When we drill into either of these counts, we’d like to show the fields id, name, and city. If we declare the fields literally, we could:

view: customers { measure: count { type: count drill_fields: [id, name, city] } measure: in_california_count { type: count drill_fields: [id, name, city] filters: { field: state value: "California" } } }

If we wanted to add a new field (say the field customers.state), we’d have to edit both lists. Instead, LookML provides a way to create named sets that we can maintain in one place and use in multiple places.

The code below creates a set customers.detail and points both counts to the same set of fields.

view: customers { set: detail { fields: [id, name, city] # creates named set customers.detail }   measure: count { type: count drill_fields: [detail*] # show fields in the set "customers.detail" } measure: in_california_count { type: count drill_fields: [detail*] # show fields in the set "customers.detail" filters: { field: state value: "California" } } }

LookML sets are pretty powerful:

Read the full sets reference

Filtering Result Sets

LookML provides a set of filter operations that can be applied to fields and explores to filter result sets before they are returned to the user.

always_filter on the Explore

Use always_filter to always apply a set of filters to any query run within an explore. The filters will appear in the Looker UI, and though users can change the default filter value that you provide, they cannot remove the filters. Generally, these filters are used to remove data that you normally don’t want to included. For example, suppose in orders, we only wanted to see orders that were complete or pending. We could add:

explore: orders { view_name: order always_filter: { filters: { field: status value: "complete,pending" } } }

If the user wanted to see orders with other status values, they could set ORDERS Status to % in the UI.

sql_always_where on the Explore

If you want to apply a query restriction that users cannot change, you can use sql_always_where. In addition to queries run by human users, the restriction will apply to dashboards, scheduled Looks, and embedded information that relies on that explore. A sql_always_where condition is not displayed to the user, unless they look at the underlying SQL of any queries that they create.

The following example prevents users from looking at orders before 2012-01-01:

# Using Looker references explore: order { sql_always_where: ${created_date} >= '2012-01-01' ;; }   # Using raw SQL explore: order { sql_always_where: DATE(created_time) >= '2012-01-01' ;; }

conditionally_filter on the Explore

Very large tables require some thought when querying, since unlimited queries can quickly become too burdensome on the database. LookML provides a way to address this in the form of conditionally_filter.

You use the conditionally_filter parameter to apply a filter to the query unless the user has already added a filter for one of the fields listed in the unless section.

The following example won’t make any change to the user’s query if the user applied a filter on one or more of these fields: created_date, shipped_time, shipped_date,, or If the user didn’t filter on any of those fields, Looker will automatically add a filter on orders.created_time of 1 day.

conditionally_filter: { filters: { field: orders.created_time value: "1 day" } unless: [created_date, shipped_time, shipped_date,,] }