home User Guide Getting Started Help Center Documentation Community Training Certification
Looker keyboard_arrow_down
language keyboard_arrow_down
Creating Looker expressions

If your admin has enabled the Custom Fields Labs feature, you can use the following features to quickly perform common functions without creating Looker expressions:

  • Quick Calculations to quickly perform common calculations on numeric fields that are in an Explore’s data table
  • Custom groups to quickly group values by custom labels without needing to develop CASE WHEN logic in sql parameters or type: case fields
  • Custom bins to group numeric type dimension values in custom tiers without needing to develop type: tier LookML fields

Looker expressions

Looker expressions (sometimes referred to as Lexp) are used to perform calculations for:

A Looker expression is built from a combination of these elements:

Creating Looker expressions

Table calculations, custom fields, and custom filters use the Looker expression editor. As you type your expression, Looker prompts you with functions, operators, and field names that you might want to use.

Handy Tip: If you are a Looker developer creating a data test to verify the logic of your model, you can use the Looker expression editor to build a Looker expression, then copy the expression into your data test’s expression parameter.

The following sections show how to use the Looker expression editor.

Seeing all suggestions

Access the Looker expression editor in an Explore by creating a table calculations, custom field, or custom filter. Type a space to see a list of all the fields, functions, and operators that you can choose from. Fields that are currently in use in an Explore will appear at the top of the list.

You can start typing to shorten the list to the items you’re interested in. Any currently in-use fields that surface in search results will be marked with a black circle.

The Lexp editor for custom fields will display Explore fields that are currently in use, if they are compatible with the custom field’s function. For example, the Explore below contains two dimensions and one measure:

When you’re creating a custom dimension, only the dimensions are surfaced in the Lexp editor, because they are the only fields eligible to be used:

Adding a field

To include a Looker field in your expression, start typing the field’s name. As you type, the editor will narrow your search to a list of fields and functions that contain what you’ve typed. You can type the name of the field as it appears on the Explore page, or you can use its LookML name if you know it.

When you select a field from the list, Looker adds it to your expression using the LookML name in the form ${view_name.field_name}. This ensures that all your fields have unique names in your expression.

You can read more detailed instructions about using fields in the Using fields section on this page.

Adding totals

If you are creating an expression based on an Explore where you displayed totals, you can also include column and row totals in your expression. Column totals appear in the editor with the word Total in front of the LookML iteration of the field name. The Count column total appears as the first suggestion below:

The LookML name for totals is in the form ${view_name.field_name:total}, where :total is added to the end of the field name. For row totals, the words Row Totals appear in front of the field name in the editor; and, in the LookML name of the field, :row_total is added to the end of the field name.

Adding operators

You can add logical operators like AND, OR, and NOT to your expression if needed. Ordinarily AND operators are evaluated before OR operators, but you can override this behavior by using parentheses. You also can use comparison operators (such as >, =, and <=) and mathematical operators (such as + and *).

When your cursor is on an operator, you can check the notes that are displayed to the right of your expression in the information pane for proper use.

You can read more detailed instructions about using operators in the Using operators section on this page.

Adding functions

To include a Looker function in your expression, start typing the function’s name. As you type the editor will narrow down your search to a list of fields and functions that contain what you’ve typed.

Functions may be constructed of arguments (or variables) that require a certain type, such as a field, a number, or yes/no. When your cursor is on a function, you can check the notes that are displayed to the right of your expression in the information pane to understand which arguments you need to provide, and what type they need to be.

To see the full list of functions that Looker offers, check out our Looker functions and operators documentation page.

You can read more detailed instructions about using functions in the Using functions section on this page.

Using error hints and the information pane

Note that as you type an expression, Looker displays an information pane to the right. This pane provides documentation and suggestions, especially if you have an error in your expression.

As shown above, the editor provides:

Including comments

You can include comments in Looker expressions by beginning the comment line with #:

Using fields

Sometimes you’ll want to use the value of a field (a dimension, measure, or table calculation) in an expression. You might want to add the value of the field to something else, check that it has a certain value, include it in a function, or many other possibilities.

As described above, just type the name of the field into the expression editor, and Looker will help you find the correct way to reference it. When you add a field to an expression, Looker uses the field’s LookML identifier, which looks like ${view_name.field_name}. This identifier does not always match the name of the field in the field picker, but it’s okay if you don’t know what it is. Just type the field name as it appears in the field picker and the expression editor will show you the field picker name and the LookML identifier together.

There are several ways you might want to retrieve a value:

When referencing fields in a custom filter, you can use only dimensions — not measures, totals, or table calculations. Additionally, you cannot refer to values in other rows or pivoted columns. This is because Looker must be able to turn your filter into SQL in order to retrieve your data.

Not all time and date options are supported in custom filters or custom fields. See the dimension group parameter description for more information.

When referencing fields in a table calculation, you can reference any value from any dimension, measure, or other table calculation.

Using operators

Looker expressions sometimes include logical, comparison, and mathematical operators to help you create different conditions:

These are usually straightforward to use. Just keep in mind that AND logic is considered before OR logic, unless you specify otherwise with parentheses. Thus the following expression without additional parentheses:

if (
  ${order_items.days_to_process}>=4 OR
  ${order_items.shipping_time}>5 AND
"review", "okay")

would be evaluated as:

if (
  ${order_items.days_to_process}>=4 OR
  (${order_items.shipping_time}>5 AND ${order_facts.is_first_purchase}),
"review", "okay")

The other thing to keep in mind is how true and false is handled. In Looker, you should use yes and no instead of true and false. These logical constants are not the same thing as the words "yes" and "no", which are enclosed in quotes. See the logical constants description for more detail.

Using functions

Looker expressions often include one or more functions, which help you to retrieve certain data or calculate certain things. They are similar in nature to Excel functions, so if you’ve used Excel functions, you’ll be comfortable with Looker functions.

If you are not familiar with functions, they take the form of a name followed by two parentheses, like this: my_function(). Sometimes you’ll need to provide information within those parentheses, separated by commas. These bits of information are called “arguments” and look like this: my_function(argument_1, argument_2).

For example, the now function does not take any arguments, and gives you the current date and time. You’d use it like this: now().

In contrast, the round function does take one argument, which is a number. You’d use it like this: round(3.2). The result you would get is 3.

There are two ways to know which arguments you’ll need to provide, if any:

To solidify this concept, consider the contains function, which has documentation that looks like this:

Function Syntax Purpose
contains contains(string, search_string) Returns Yes if string contains search_string, and No otherwise

You can see that two arguments are required. They have the names string and search_string, but that doesn’t mean you need to type the exact word “string” and “search_string” into the function. These are just names for the arguments that you’ll replace with something. Reading the purpose, we see that string should be a field or other value we want to search in, while the search_string is the thing we want to search for. An example might be:

contains(${customer.feedback_text}, "great")

If the word “great” appears in the customer feedback this function will give us a result of Yes, otherwise it will give a result of No.

The final thing to know is that you can put functions inside of other functions to handle whatever complex logic you want. As long as the result of the inner function is appropriate for the arguments of the outer function, it will work. For example:


Here we’ve nested the is_null function inside of an if function, which is itself inside a contains function. It works like this:

  1. The is_null() function checks whether we have customer feedback text.
  2. Next, the if() function uses that result and returns the customer feedback text if we have it, or otherwise returns the customer comment text.
  3. Finally, the contains() function uses the text returned by the if() function and searches it for the word “great”.

Putting it all together, logically this expression means “If we have customer feedback then search in that, but if we don’t, search in customer comments instead. In both cases, look for the word ‘great’ “.

Custom filters and custom fields can use most functions, but they cannot use some mathematical functions, or functions that refer to other rows or pivot columns. The Looker functions and operators documentation page lets you know which functions you can use.

You can use any function in a table calculation. You can also use any function in the expression parameter of a data test, since the expression parameter is essentially a table calculation that results in a yesno (Boolean).


Now that you’ve seen how Looker expressions enable you to make the most of your table calculations, custom fields, and custom filters for an Explore, let’s look at how we can merge results from multiple Explores.