13. Analytics Pro functions and operators

This feature is only available for the following plans:
PROFESSIONAL
PERFORMANCE
Click here for more information on our plans.
 

A major part of creating expressions in Analytics Pro is the functions and operators you can use in them. The functions and operators are 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

Función  Sintaxis Objetivo
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_decimals decimal places.
sqrt sqrt(value) Returns the square root of value.

 

Operators for any Analytics Pro expression

Operador Sintaxis Objetivo
+ value_1 + value_2 Adds value_1 and value_2.
- value_1 - value_2 Subtracts value_2 from value_1.
* value_1 * value_2 Multiplies value_1 and value_2.
/ value_1 / value_2

Divides value_1 by value_2.

 

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 if a word or letter is in a phrase, or replace elements of a word or phrase.

Función  Sintaxis Objetivo
concat concat(value_1, value_2, ...) Returns value_1, value_2, ..., value_n joined as one string.
contains contains(string, search_string) Returns Yes if string contains search_string, and No otherwise.
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_string in string if it exists, and 0 otherwise.
replace replace(string, old_string, new_string) Returns string with all occurrences of old_string replaced with new_string.
substring substring(string, start_position, length) Returns the substring of string, beginning at start_position, consisting of length characters. The start_position starts at 1, with 1 indicating the first character in the string, 2 indicating the second character in the string, and so on.
upper upper(string)

Returns string with all characters converted to uppercase.

 

Date functions

Date functions enable you to work with dates and times.

Función  Sintaxis Objetivo
add_days add_days(number, date) Adds number days to date.
add_hours add_hours(number, date) Adds number hours to date.
inutes add_minutes(number, date) Adds number minutes to date.
add_monthadd_ms add_months(number, date) Adds number months to date.
add_seconds add_seconds(number, date) Adds number seconds to date.
add_years add_years(number, date) Adds number years to date.
date date(year, month, day) Returns “year-month-day” date or null if the date would be invalid.
date_time date_time(year, month, day,
hours, minutes, seconds)
Returns
“year-month-day hours:minutes:seconds” date or null if the date would be invalid.
diff_days diff_days(start_date, end_date) Returns the number of days between start_date and end_date.
diff_hours diff_hours(start_date, end_date) Returns the number of hours between start_date and end_date.
diff_minutes diff_minutes(start_date, end_date) Returns the number of minutes between start_date and end_date.
diff_months diff_months(start_date, end_date) Returns the number of months between start_date and end_date.
diff_seconds diff_seconds(start_date, end_date) Returns the number of seconds between start_date and end_date.
diff_years diff_years(start_date, end_date) Returns the number of years between start_date and end_date.
extract_days extract_days(date) Extracts the days from date.
extract_hours extract_hours(date) Extracts the hours from date.
extract_minutes extract_minutes(date) Extracts the minutes from date.
extract_months extract_months(date) Extracts the months from date.
extract_seconds extract_seconds(date) Extracts the seconds from date.
extract_years extract_years(date) Extracts the years from date.
now now() Returns the current date and time.
trunc_days trunc_days(date) Truncates date to days.
trunc_hours trunc_hours(date) Truncates date to hours.
trunc_minutes trunc_minutes(date) Truncates date to minutes.
trunc_months trunc_months(date) Truncates date to months.
trunc_years trunc_years(date) Truncates date to years.

 

Logical functions, operators, and constants

Función  Sintaxis Objetivo
case case(when(yesno_arg, value_if_yes), when(yesno_arg, value_if_yes), ..., else_value) Allows conditional logic with multiple conditions and outcomes. Returns value_if_yes for the first when case whose 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.

 

The following comparison operators can be used with any data type:

Operador Sintaxis Objetivo
= value_1 = value_2 Returns Yes if value_1 is equal to value_2, and No otherwise.
!= value_1 != value_2 Returns Yes if value_1 is not equal to value_2, and No otherwise.

 

The following comparison operators only can be used with numbers and dates:

Operador Sintaxis Objetivo
> value_1 > value_2 Returns Yes if value_1 is greater than value_2, and No otherwise.
< value_1 < value_2 Returns Yes if value_1 is less than value_2, and No otherwise.
>= value_1 >= value_2 Returns Yes if value_1 is greater than or equal to value_2, and No otherwise.
<= value_1 <= value_2 Returns Yes if value_1 is less than or equal to value_2, and No otherwise.

 

You also can combine Analytics Pro expressions with these logical operators:

Operador Sintaxis Objetivo
AND value_1 AND value_2 Returns Yes if both value_1 and value_2 are Yes, and No otherwise.
OR value_1 OR value_2 Returns Yes if either value_1 or value_2 is Yes, and No otherwise.
NOT NOT value Returns Yes if value is No, and No otherwise.

 

Logical constants

You can use logical constants in Analytics Pro expressions. These constants are always written in lowercase and have the following meanings:

Constante Significado
yes True
no False
null No value

 

Note that the constants yes and no are the special symbols that ​mean true or false in Analytics Pro 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")

Was this article helpful?
Sign up to give your opinion about our articles.