Full List of Expressions in the Analytics Editor

This article contains the full list of expressions that are available to use in the Analytics Editor.

Using expressions in analyses enables you to utilize your data in different ways or manipulate it into different forms.

Available Expressions in the Analytics Editor

All examples are based on app Completions. So, when you use a MAXIMUM() or MINIMUM() function, for example, you are looking for the maximum or minimum value of a Variable across all completions of an app.

SUBSTITUTE()
Within a string given in the first argument, replace all examples of the second argument with the third argument
Ex. SUBSTITUTE("A734", "7", "3")
This will return "A334"

TRIM()
Removes a substring (specified by the second argument) from the front and back of the first argument.

Typecasting Expressions

DATETIMETOTEXT()
Converts a Datetime value to a text value that is easily readable by a human
Ex. DATETIMETOTEXT(datetime_variable, "MMMM DD, YYYY", 'America/New_York')
This will convert a datetime to Eastern Standard Time and show it in traditional date formatting like "January 1, 2019".

Note: The last parameter, the time zone, must come from one of the tz database names listed here.

SECONDS_TO_INTERVAL()
Converts seconds into an Interval.

SECONDS_TO_INTERVAL(seconds: Integer or Number) => Interval

Ex. SECONDS_TO_INTERVAL(20) => 0:00:20

TEXTTOBOOLEAN()
Parses text into a Boolean.

"y", "yes", "t", "true" and "on" and any capitalized variants are recognized as true, and "n", "no", "f" "false" and "off" are recognized as false.

Ex. TEXTTOBOOLEAN("yes")

TEXTTODATETIME()
Converts a text value into a Datetime value with the specified parameters. Commonly used if you have a Connector Function that shares a Datetime in text format and you need to convert it.

TEXTTOINTEGER()
Parses text or Interval into an Integer.

Ex. TEXTTOINTEGER("123")

TEXTTONUMBER()
Parses text or timestamp into a Number

Ex. TEXTTONUMBER("2.4")

TOTEXT()
Parses anything into text.

Ex. TOTEXT(2)

Truncated Expressions

CEIL()
Rounds up to the nearest Integer

FLOOR()
Rounds down to the nearest Integer

ROUND()
Rounds a number to the specified number of decimal places. Maximum of 3 decimal places.
Ex. 1 - ROUND(123.123456, 1)
This will return 123.1
Ex. 2 - ROUND(123.12)
This will return 123

ROUNDDATETIME()
Truncates a Datetime value to a given unit, like month or day.
Ex. ROUNDDATETIME(datetime_variable, "Month", "America/New_York")
This will convert a datetime to just the month in Eastern Standard Time.

Note: The last parameter, the time zone, must come from one of the tz database names listed here.

Returned Value Expressions

AVERAGE()
Returns the average of arguments across all completionsCompletion.
Ex. AVERAGE(weight)

AVG()
Alias for AVERAGE()

AVERAGEFILTERED()
Returns the average of arguments across all completions, filtered to match a condition
Ex. AVERAGEFILTERED(weight, temperature > 70)

AVGFILTERED()
Alias for AVERAGEFILTERED()

COUNT()
Returns the number of completions matching condition.
Ex. COUNT(temperature > 70)

COUNTOFCOMPLETIONS()
Returns the total number of Completion. Does not take an argument.
Ex. COUNTOFCOMPLETIONS()

FIND()
Returns the position for where the first argument occurs within the second argument.

IF()
Based on the first argument, returns the second argument if first argument is true, or third argument if first argument is false.
Ex. IF(true, 1, 2)

ISNULL()
Returns true if the argument is null, otherwise false.

LEFT()
Returns a substring of a text value, beginning at the position specified by the second argument.

LEN()
Returns the length of a text value.

LINK()
Returns the first of the arguments that is not null.
Ex. LINK(weight, 70)
This will return the value of weight if weight has a value

MAXIMUM()
Returns the maximum of arguments across all completions
EX. MAXIMUM(weight)

MAX()
Alias for MAXIMUM()

MAXIMUMFILTERED()
Returns the maximum of arguments across all completions, filtered to match a condition
Ex. MAXIMUMFILTERED(weight, temperature > 70)

MAXFILTERED()
Alias for MAXIMUMFILTERED()

MID()
Returns a substring of a text value, starting at the position specified by the second argument. The third argument specifies the length of the return.

MINIMUM()
Returns the minimum of arguments across all completions
EX. MINIMUM(weight)

MIN()
Alias for MINIMUM()

MINIMUMFILTERED()
Returns the minimum of arguments, filtered to match a condition
Ex. MINIMUMFILTERED(weight, temperature > 70)

MINFILTERED()
Alias for MINIMUMFILTERED()

NULLIF()
The NULLIF function returns a null value if argument_1 equals to argument_2, otherwise it returns argument_1.
Ex. NULLIF(argument_1, argument_2)
This would return null if argument_1 was 1 and argument_2 was 1

RIGHT()
Returns a substring of a text value, ending at the position specified by the second argument.

SUM()
Returns the sum of arguments across all completions.
Ex. SUM(weight)

SUMFILTERED()
Returns the sum of arguments across all completions, filtered to match a condition
Ex. SUMFILTERED(weight, temperature > 70)

Array Expressions

REGEX_MATCH()
Returns an array of regular expression matches of a text value. Matches based on the given regular Expression and modifiers. This method is based on the match JavaScript method and has 2 "modes".

When this method is given 2 arguments, it is equivalent to the following JavaScript expression:

text.match(new RegExp(regex))

When the optional parameter modifiers is provided, this method is equivalent to the following expression in JavaScript:

text.match(new RegExp(regex, modifiers))

Modifiers are flags that can be used to modify the behavior of the regex search.

REGEX_REPLACE()
Returns an array of regular expression matches of a text value. Matches based on the given regular Expression and modifiers. This method is based on the replace JavaScript method.

When the function is given 3 arguments, it is equivalent to the following JavaScript expression:

text.replace(new RegExp(regex), replacement)

.
When the optional parameter modifiers is provided, this method is equivalent to the following expression in JavaScript:

text.replace(new RegExp(regex, modifiers), replacement)

Modifiers are flags that can be used to modify the behavior of the regex search.

SPLIT()
Splits a text value into an Array by a specified delimiter.

Did you find what you were looking for?

You can also head to community.tulip.co to post your question or see if others have faced a similar question!