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
...
💡 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 insql
parameters ortype: case
fieldsCustom bins to group numeric type dimensions in custom tiers without needing to develop
type: tier
LookML fields
Anchor | ||||
---|---|---|---|---|
|
...
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
.
...
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
...
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 | ||||
---|---|---|---|---|
|
...
Consider the contains
function, which has documentation that looks like this:
Function | Syntax | Purpose |
---|---|---|
contains |
| Returns |
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:
...
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).