Table of Contents
- Overview
- Mathematical Functions and Operators
- String Functions
- Date Functions
- Logical functions, operators, and constants
- Logical Constants
- Combining AND and OR Operators
- Filter Functions for Custom Filters and Custom Fields
Overview
BTBI Creator expressions are used to perform calculations for:
A major part of these expressions is the functions and operators that you can use in them. The functions and operators can be divided into a few basic categories:
- Mathematical: Number-related functions
- String: Word- and letter-related functions
- Dates: Date- and time-related functions
- Logical transformation: Includes boolean (true or false) functions and comparison operators
Mathematical functions and operators
Mathematical functions and operators work in one of two ways:
- Some mathematical functions perform calculations based on a single row. For example, rounding, taking a square root, multiplying, and similar functions can be used for values in a single row, returning a distinct value for each and every row. All mathematical operators, such as
+
, are applied one row at a time. - Other mathematical functions, like averages and running totals, operate over many rows. These functions take many rows and reduce them to a single number, then display that same number on every row.
Function for Any BTBI Creator Expression
Function | Syntax | Purpose |
abs | abs(value) | Returns the absolute value of value . |
ceiling | ceiling(value) | Returns the smallest integer greater than or equal to value . |
exp | exp(value) | Returns e to the power of value . |
floor | floor(value) | Returns the largest integer less than or equal to value . |
ln | ln(value) | Returns the natural logarithm of value . |
log | log(value) | Returns the base 10 logarithm of value . |
mod | mod(value, divisor) | Returns the remainder of dividing value by divisor . |
power | power(base, exponent) | Returns base raised to the power of exponent . |
rand | rand() | Returns a random number between 0 and 1. |
round | round(value, num_decimals) | Returns value rounded to num_ decimal places. |
sqrt | sqrt(value) | Returns the square root of value . |
Operators for any BTBI Expression
You can use the following standard mathematical operators:
Operator | Syntax | Purpose |
+ | value_ |
Adds value_ and value_ . |
- | value_ |
Subtracts value_ from value_
|
* | value_ |
Multiplies value_ and value_ . |
/ | value_ |
Divides value_ by value_ . |
String Functions
String functions operate on sentences, words, or letters, which are collectively called "strings." You can use string functions to capitalize words and letters, extract parts of a phrase, check to see if a word or letter is in a phrase, or replace elements of a word or phrase. String functions can also be used to format the data returned in the table.
Functions for Any BTBI Creator Expression
Function | Syntax | Purpose |
concat | concat(value_1, value_2, ...) | Returns value_ , value_ , . , value_ joined as one string. |
contains | contains(string, search_string) | Returns Yes if string contains search_ , and No otherwise. The contains function is case-sensitive. |
length | length(string) | Returns the number of characters in string . |
lower | lower(string) | Returns string with all characters converted to lowercase. |
position | position(string, search_string) | Returns the start index of search_ in string if it exists, and 0 otherwise. |
replace | replace(string, old_string, new_string) | Returns string with all occurrences of old_ replaced with new_ . |
substring | substring(string, start_position, length) | Returns the substring of string , beginning at start_ , consisting of length characters. The start_ starts at 1 , with 1 indicating the first character in the string, 2 indicating the second character in the string, and so on. |
upper | uppser(string) | Returns string with all characters converted to uppercase. |
Date Functions
Date functions enable you to work with dates and times.
Functions for Any BTBI Creator Expression
Function | Syntax | Purpose |
add_days | add_days( |
Adds number days to date . |
add_hours | add_hours( |
Adds number hours to date . |
add_minutes | add_minutes( |
Adds number minutes to date . |
add_months | add_months( |
Adds number months to date . |
add_seconds | add_seconds( |
Adds number seconds to date . |
add_years | add_years( |
Adds number years to date . |
date | date( |
Returns "year-month-day " date or null if the date would be invalid. |
date_time | date_time( |
Returns year-month-day hours:minutes:seconds date or null if the date would be invalid. |
diff_days | diff_days( |
Returns the number of days between start_ and end_ . |
diff_hours | diff_hours( |
Returns the number of hours between start_ and end_ . |
diff_minutes | diff_minutes( |
Returns the number of minutes between start_ and end_ . |
diff_months | diff_months( |
Returns the number of months between start_ and end_ . |
diff_seconds | diff_seconds( |
Returns the number of seconds between start_ and end_ . |
diff_years | diff_years( |
Returns the number of years between start_ and end_ . |
extract_days | extract_days( |
Extracts the days from date . |
extract_hours | extract_hours( |
Extracts the hours from date . |
extract_minutes | extract_minutes( |
Extracts the minutes from date . |
extract_months | extract_months( |
Extracts the months from date . |
extract_seconds | extract_seconds( |
Extracts the seconds from date . |
extract_years | extract_years( |
Extracts the years from date . |
now | now() |
Returns the current date and time. |
trunc_days | trunc_days( |
Truncates date to days. |
trunc_hours | trunc_hours( |
Truncates date to hours. |
trunc_minutes | trunc_minutes( |
Truncates date to minutes. |
trunc_months | trunc_months( |
Truncates date to months. |
trunc_years | trunc_years( |
Truncates date to years. |
Logical functions, operators, and constants
Logical functions and operators are used to assess whether something is true or false. Expressions using these elements take a value, evaluate it against some criteria, return Yes
if the criteria are met, and No
if the criteria are not met. There are also various logical operators for comparing values and combining logical expressions.
Functions for Any BTBI Creator Expression
Function | Syntax | Purpose |
case | case(when(yesno_arg, value_if_yes), when(yesno_arg, value_if_yes),..., else_value) | Allows conditional logical with multiple conditions and outcomes. Returns value_if_yes for the first when case who yesno_arg value is yes. Returns else_value if all when cases are no. |
coalesce | coalesce(value_1, value_2, ...) | Returns the first non-null value in value_1, value_2, ..., value_n if found and null otherwise. |
if | if(yesno_expression, value_if_yes, value_if_no) | If yesno_expression evaluates to Yes, returns the value_if_yes value. Otherwise, returns the value_if_no value. |
is_null | is_null(value) | Returns Yes if value is null and No otherwise. |
Operators for Any BTBI Creator Expression
The following comparison operators can be used with any data type:
Operator | Syntax | Purpose |
= | value_1 = value_2 | Returns Yes if value_ is equal to value_ , and No otherwise. |
!= | value_1 != value_2 | Returns Yes if value_ is not equal to value_ , and No otherwise. |
The following comparison operators can be used with numbers, dates, and strings:
Operator | Syntax | Purpose |
> | value_1 > value_2 | Returns Yes if value_ is greater than value_ , and No otherwise. |
< | value_1 < value_2 | Returns Yes if value_ is less than value_ , and No otherwise. |
>= | value_1 >= value_2 | Returns Yes if value_ is greater than or equal to value_ , and No otherwise. |
<= | value_1 <= value_2 | Returns Yes if value_ is less than or equal to value_ , and No otherwise. |
You can also combine BTBI Creator Expressions with these logical operators:
Operator | Syntax | Purpose |
AND | value_1 AND value_2 | Returns Yes if both value_ and value_ are yes, and No otherwise. |
OR | value_1 OR value_2 | Returns Yes if either value_ or value_ are yes, and No otherwise. |
NOT | NOT value | Returns Yes if value is No, and No otherwise. |
Note: These logical operators must be capitalized. Logical operators written in lowercase will not work.
Logical Constants
You can use logical constants in Looker expressions. These constants are always written in lowercase and have the following meanings:
Constant | Meaning |
yes | True |
no | False |
null | No value |
Note that the constants yes
and no
are the special symbols that mean true or false in Looker expressions. In contrast, using quotes such as in "yes"
and "no"
creates literal strings with those values.
Logical expressions evaluate to true or false without requiring an if
function. For example, this:
if(${field} > 100, yes, no)
is equivalent to this:
${field} > 100
You also can use null
to indicate no value. For example, you may want to determine if a field is empty, or assign an empty value in a certain situation. This formula returns no value if the field is less than 1, or the value of the field if it is more than 1:
if(${field} < 1, null, ${field})
Combining AND and OR Operators
AND
operators are evaluated before OR
operators, if you don't otherwise specify the order 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")
Filter Functions for Custom Filters and Custom Fields
Filter functions let you work with filter expressions to return values based on filtered data. Filter functions work in custom filters, filters on custom measures, and custom dimensions, but are not valid in table calculations.
Function | Syntax | Purpose |
matches_filter | matches_filter( |
Returns Yes if the value of the field matches the filter expression, No if not. |
Comments
0 comments
Please sign in to leave a comment.