Creating expressions
Create custom table calculations or custom filters by using expressions to define the logic.
An expression is built from a combination of these elements:
- NULLs: 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.
- Constants: A constant is an unchanging value that you provide. A number such as 7 or a string such as Completed are constants.
- Fields: A reference to a field, including dimensions, measures, and table calculations.
- Operators: Mathematical, statistical, comparison, and logical operators.
- Functions: Use functions to transform your data or reference data in complex ways. Similar to Excel functions.
View all suggestions
Enter a space in the table calculation editor to see a list of all fields, functions, and operators that you can choose from. Fields that are currently in use are marked with a black circle and appear at the top.
Start typing to shorten the list to items that you are interested in.
Add fields
To include a 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 have typed. You can type the name of the field as it appears in the field picker, or you can use its internal name if you know it.
When you select a field from the list, your expression is added with
the internal name in the form
${view_name.field_name}. This ensures that all of your
fields have unique names in your expression.
Add totals
If you are creating an expression where your results include 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 internal field name,
and
:total is added to the end of the field name. For
example,
Count -
Total ${orders.count:total}
Add 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
*).
Add functions
To include a 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.
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 display 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.