Content Comparison

The content in this article is appropriate for: Administrators and Supervisors

This article can also be found on our partner website: https://cloud.google.com/looker/docs/creating-looker-expressions

...

• What are Expressions

• Creating Expressions

  • Using Fields

  • Using Operators

  • Using Functions

💡 Note: If your admin has granted you permissions to create table calculations, you can use the following features to quickly perform common functions without creating Looker expressions:

• Shortcut Calculations to quickly perform common calculations on numeric fields that are in an Explore's data table

If your admin has granted you the permissions to create custom fields, you can use the following features to quickly perform common functions without needing to create Looker expressions:

• Custom groups to quickly group values under custom labels without needing to develop CASE WHEN logic in sql parameters or type: case fields

• Custom bins to group numeric type dimensions in custom tiers without needing to develop type: tierLookML fields

Anchor
What What

SC Analytics Expressions

SC Analytics (Looker) expressions are used to perform calculations for:

...

• 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 that you provide. A number such as 7 or a string such as Completed are constants.

• A Looker field: A reference to an SC Analytics (Looker) field, which includes dimensions, measures, and table calculations.

• A Looker operator: There are several types of operators (which are listed on the Looker functions and operators documentation page):

  • Mathematical operators (such as +, -, *, and /)

  • Comparison operators (such as =, >, and <=)

  • Logical operators (such as AND, OR, and NOT)

• A Looker function: These are similar in nature to Excel functions. Functions let you transform your data or reference data in complex ways. All available functions are listed on the Looker functions and operators documentation page.

Anchor
Creating Creating

Creating Expressions

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

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

Seeing all suggestions

Access the SC Analytics (Looker) expression editor in an Explore by creating a table calculations, custom field, or custom filter.

...

The editor for custom fields displays Explore fields that are currently in use, if they are compatible with the custom field's function.

Adding a field

To include an SC Analytics (Looker) field in your expression, start typing the field's name. As you type, the editor narrows 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, SC Analytics (Looker) adds it to your expression using the LookML name in the form ${view_name.field_name}. This ensures that all of your fields have unique names in your expression.

💡Note: 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 that is 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. For example, for a field named Count, SC Anlaytics will give the column total for that field the name Count - Total.

...

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, like ${view_name.field_name:row_total}.

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 you hover your cursor over an operator, notes for proper use display in the information pane.

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

Adding functions

To include an SC Analytics function in your expression, start typing the function's name. As you type, the editor narrows your search to a list of fields and functions that contain what you've typed.

...

You can reference the full list of functions that SC Analytics offers on the Looker functions and operators documentation page.

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

Using error hints and the information pane

SC Analytics (Looker) displays an information pane next to the expression editor. This pane provides documentation and suggestions, especially if you have an error in your expression.

...

• Error highlighting: SC Analytics underlines in red any parts of the expression that are not yet correct.

• Suggestions and error Details: SC Analytics 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: SC Analytics 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, SC Analytics provides the information that the first argument should evaluate as true or false. You can click on the function name to navigate to the documentation for that function.

Including comments

You can include comments in SC Analytics expressions by beginning the comment line with # in the expression editor.

Anchor
Fields Fields

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.

...

• 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 this list of positional functions). The offset function might look like this: offset(${product.category}, -1).

• Get a value from a pivoted column: You can also 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 this list of pivot functions). The pivot function might look like this: pivot_index(${order.total_sales}, 1).

• Get a total from a row or a column: If you added totals to your Explore, you can get total values from the column or row by adding :total (for column totals) or :row_total (for row totals) to the field name, using the format ${field_name:total}. For example, if you want a percentage of the total of an Orders count, you could create a table calculation like this: ${orders.count} / ${orders.count:total}.

💡 Note: 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 SC Analytics 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.

Anchor
Operators Operators

Using Operators

SC Analytics (Looker) expressions can include logical, comparison, and mathematical operators to create different conditions:

...

In SC Analytics, 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.

Anchor
Functions Functions

Using Functions

SC Analytics (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.

...

Logically, this expression means: "If there is customer feedback, then search in that. If not, then search in customer comments instead. In both cases, look for the word 'great' ".

💡 Note: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).