Creating Looker Expressions

On this Page
Docs Menu

Looker Expressions

Table calculations and custom filters both rely on Looker Expressions (sometimes referred to as “lexp”) to perform calculations. This page explains, in detail, how to write these expressions.

A Looker Expression is built up by using a combination of these elements:

  • NULL - The value NULL indicates there is no data, and can be useful when you want to check that something is empty or doesn’t exist.

  • A Constant - A constant is an unchanging value you provide. A number such as 7, or a string such as Completed are constants.

  • A Looker Field - A reference to a Looker field, which includes dimensions, measures, and table calculations.

  • A Looker Function - These are similar in nature to an Excel function, if you’re familiar with those. Functions allow you to transform your data or reference data in complex ways. All of the available functions are listed on our Looker Functions and Operators page.

  • A Looker Operator - there are several types of operators (which are listed on our Looker Functions and Operators page):

    • Mathematical operators (such as +, -, *, and /)
    • Comparison operators (such as =, >, and <=)
    • Logical operators (such as AND, OR, and NOT)

Creating Looker Expressions

Table Calculations and Custom Filters both 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. It works like this:

Seeing All Suggestions

Type a space to see a list of all the fields, functions, and operators that you can choose from. It’s quite a long list, so you will typically want to start typing to shorten the list to the items you’re interested in.

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 if you happen to know its LookML name, you can use that instead.

When you actually select a field from the list, Looker will add it into your expression using the LookML name, which looks like ${view_name.field_name}. This may seem a little odd if you aren’t used to writing LookML, but Looker does this is to ensure that all of your fields have unique names in your expression.

You can read more detailed instructions about using fields below.

Adding Operators

If needed, you can add logical operators like AND, OR, and NOT to your expression to get the logic you need. Keep in mind that AND operators are evaluated before OR operators, although 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 below.

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 might require some number of arguments (the things you put inside the function), and if so, they must be of certain types (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 page.

You can read more detailed instructions about using functions below.

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:

  • Error Highlighting: In the editor itself, Looker underlines in red any parts of the expression that are not yet correct.

  • Suggestions and Error Details: The top part of the information pane gives suggestions about what to add next in your expression. If there’s an error, it explains why the error is occurring. If there are multiple errors, the error that it shows to you is based on the location of your cursor.

  • Documentation: In the lower part of the information pane, Looker displays documentation about the function or operator you’re working with, based on your cursor position. For example, while you type the first argument of an if() function, Looker provides the information that the first argument should evaluate as true or false. You can click on the function name in that section to navigate to the documentation for that function.

Using Fields

Sometimes you’ll want to use the value of a field - which includes dimensions, measures, and table calculations - in an expression. You might want to add the value of the field to something else, check it is 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 its LookML identifier, which looks like ${view_name.field_name}. This identifier does not always match the way the field appears in the field picker, but it’s okay if you don’t know it. Just type the 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:

  • Get a Value from the Same Row: The most basic way to use a field is to reference it directly. For example, your expression might use ${product.category}. When you do this, you’re saying “for any given row, grab the Product Category from that row”.

  • Get a Value from a Different Row: You can also get a field’s value from a different row. For example, you might want the logic “for any given row, grab the Product Category from the previous row”. To do that, you can use an offset function (see a list here). It might look like this: offset(${product.category}, -1).

  • Get a Value from a Pivoted Column: Finally, you can get values from pivoted columns. For example, you might want the logic “for any given row, grab the Total Sales from the first pivoted column”. To work with pivoted columns, you’ll need to use pivot functions (see a list here). It might look like this: pivot_index(${order.total_sales}, 1).

When referencing fields in a custom filter you may only use dimensions, not measures 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.

When referencing fields in a table calculation you may 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:

  • Logical operators are things like AND, OR, and NOT
  • Comparison operators are things like > and <
  • Mathematical operators are things like + and -

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
  ${order_facts.is_first_purchase},
"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 our 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:

  • The information pane that appears to the right of the expression editor will provide some documentation about the function you are writing. You can click on the function’s name to navigate to its documentation.
  • You can also navigate directly to the Looker Functions and Operators page and look up the function you want to use.

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. Just as long as the result of the inner function is appropriate for the arguments of the outer function it will work. For example:

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

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 or not 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 can use most functions. They cannot use some mathematical functions, or the functions that refer to other rows or pivot columns. Our Looker Functions and Operators page lets you know which functions you can use.

When using functions in a Table Calculation you may use any function.

Conclusion

Now that you know the ins and outs of writing Looker Expressions, learn how to save and organize your work with Spaces.

Still have questions?
Go to Discourse - or - Email Support
Top