Formula functions library

+

Addition

100 + 50
= 150

-

Subtraction

100 - 50
= 50

*

Multiplication

100 * 2
= 200

/

Division

100 / 4
= 25

==

Equal to

100 == 100
= true

!=

Not equal to

100 != 50
= true

>

Greater than

100 > 50
= true

>=

Greater than or equal to

100 >= 100
= true

<

Less than

50 < 100
= true

<=

Less than or equal to

50 <= 100
= true

!

Negates a boolean value.

!true
= false

??

Returns the left value if it is not null, otherwise returns the right value. Useful for setting a fallback when an attribute might be empty.

{ARR} ?? 0

if(condition, trueValue, falseValue)

Returns trueValue when the condition is true, otherwise returns falseValue. The condition must evaluate to a boolean value.

if(true, "Enterprise", "Standard")
= Enterprise

if(false, 100 * 12, 0)
= 0

and(left, right) or left and right

Returns true only when both arguments are true. Used to combine multiple conditions.

and({Deal value} > 10000, {Deal stage} == "Negotiation")
= true

or(left, right) or left or right

Returns true when at least one of the arguments is true.

or({Deal stage} == "Closed won", {Deal stage} == "Closed lost")
= true

not(value) or !value

Returns the opposite of a boolean value. Converts true to false and false to true.

not(hasBeenIn({Deal stage}, "Closed lost"))
= true

avg(values)

Calculates the average of an array of numbers or currency values. Returns a single value of the same type as the input.

avg([1.5, 2.3, 4.7])
= 2.8333

exp(value)

Returns e raised to the power of the given number or currency value.

exp(2.5)
= 12.1825

log(value)

Returns the natural logarithm (base e) of the given number or currency value.

log(1000)
= 6.9078

abs(value)

Returns the absolute value of a number or currency value, converting negative values to positive.

abs(-42.5)
= 42.5

round(value, num_digits)

Rounds a number or currency value to the specified number of decimal places.

round(1234.5678, 2)
= 1234.57

ceil(value)

Rounds a number or currency value up to the nearest integer.

ceil(7.2)
= 8

floor(value)

Rounds a number or currency value down to the nearest integer.

floor(9.8)
= 9

max(values)

Returns the maximum value from an array of numbers, currency values, or dates.

max([10, 25, 3])
= 25

mean(values)

Returns the arithmetic mean of an array of numbers, currency values, or dates.

mean([10, 20, 90])
= 40

median(values)

Returns the median (middle value) of an array of numbers, currency values, or dates.

median([10, 20, 90])
= 20

min(values)

Returns the minimum value from an array of numbers, currency values, or dates.

min([10, 25, 3])
= 3

sum(values)

Returns the sum of an array of numbers or currency values. Returns a single value of the same type as the input.

sum([10, 25, 3])
= 38

count(values)

Returns the number of items in an array.

count([10, 25, 3])
= 3

count([])
= 0

unique(values)

Returns a new array with duplicate values removed, keeping only unique entries.

unique([1, 2, 2, 3, 3])
= [1, 2, 3]

power(base, exponent)

Returns the base raised to the power of the exponent.

power(2, 8)
= 256

mod(dividend, divisor)

Returns the remainder after dividing the dividend by the divisor.

mod(10, 3)
= 1

random()

Returns a random number between 0 and 1. Takes no arguments.

round(random() * 100, 0)

dateAdd(date, units, unit_type)

Adds a specified number of time units to a date. The unit_type must be a text value such as "days", "months", or "years".

dateAdd(date("2024-01-15"), 30, "days")
= 2024-02-14

dateAdd(date("2024-06-01"), 3, "months")
= 2024-09-01

formatDate(date, format)

Formats a date value as text using a format string. Common tokens: "YYYY" (year), "MM" (month number), "DD" (day), "MMMM" (full month name), "Q[Q] YYYY" (quarter and year).

formatDate(date("2024-01-15"), "YYYY-MM-DD")
= 2024-01-15

formatDate({Close date}, "MMMM YYYY")
= March 2026

date(raw_date)

Parses a number or text value into a date. Accepts ISO 8601 date strings or numeric timestamps.

date("2024-01-15")
= 2024-01-15

timestamp(value)

Parses a text or number value into a timestamp. Accepts ISO 8601 datetime strings or numeric Unix timestamps.

timestamp("2024-01-15T09:30:00Z")
= 2024-01-15T09:30:00Z

now()

Returns the current date and time as a timestamp. Takes no arguments. Using this function adds a daily recalculation around midnight UTC.

dateAdd(now(), 7, "days")

today()

Returns the current date without a time component. Takes no arguments. Using this function adds a daily recalculation around midnight UTC.

today()

day(date)

Extracts the day of the month from a date as a number from 1 to 31.

day(date("2024-03-15"))
= 15

hour(date)

Extracts the hour from a date or timestamp as a number from 0 to 23.

hour(timestamp("2024-03-15T14:30:00Z"))
= 14

eomonth(date)

Returns the last day of the month for the given date.

eomonth(date("2024-02-10"))
= 2024-02-29

setTimezone(date, timezone)

Converts a date or timestamp to the specified timezone, returning a timestamp.

setTimezone(now(), "Europe/London")

dateDiff(start_date, end_date, unit_type)

Returns the absolute difference between two dates or timestamps in the requested unit. The unit_type must be one of "seconds", "minutes", "hours", "days", "weeks", "months", or "years"."

dateDiff(date("2024-01-01"), date("2024-01-31"), "days") = 30

contains(haystack, needle)

Returns true when text or a text array contains a specific text value. For text, checks if the needle is a substring of the haystack. For arrays, checks if the needle exists as an element.

contains("Hello World", "World")
= true

contains(["apple", "banana", "orange"], "banana")
= true

replace(subject, search, replacement)

Replaces the first occurrence of a search term within a text value with a replacement term.

replace("Hello World", "World", "There")
= "Hello There"

replace("aabbcc", "b", "x")
= "aaxbcc"

replaceAll(subject, search, replacement)

Replaces all occurrences of a search term within a text value with a replacement term.

replaceAll("Hello World", "l", "r")
= "Herro Worrd"

replaceAll("aabbcc", "b", "x")
= "aaxxcc"

length(value)

Returns the length of a text value.

length("Hello World")
= 11

hasBeenIn(attribute, values)

Returns true when a select option or status appears in the current or historical values for that attribute. Pass a single value or an array of values. Optionally pass a match argument ("any" or "all") and a dateRange to limit the history checked.

hasBeenIn({Deal stage}, "Closed lost")
= true

hasBeenIn({Deal stage}, ["Negotiation", "Signing"])
= true

valueSetAt(attribute, value)

Returns the timestamp when the attribute was first set to the given value. Pass current to get when the current value was set. Optionally pass a position argument ("first" or "last") to get the first or last time the value was set.

valueSetAt({Deal stage}, "Negotiation")
= 2024-03-01

valueSetAt({Deal stage}, current)
= 2024-04-15

timeSpentIn(attribute, value, unit)

Returns the total time the attribute has been set to the given value across its history. Pass current to get time spent in the current value. Accepted units: "hours", "days", "weeks", "months". Months are approximated as 30 days.

timeSpentIn({Deal stage}, "Negotiation", "days")
= 14

timeSpentIn({Deal stage}, current, "hours")
= 72

valueAt(attribute, timestamp)

Returns the historical value of an attribute at a specific timestamp. Useful for finding what value an attribute had at a particular point in time.

valueAt({Deal stage}, date("2024-01-15"))
= Proposal

previousValue(attribute)

Returns the value the attribute had immediately before its current value. Returns an empty value when the attribute has no prior history. Actor reference and interaction attributes are not supported.

previousValue({Deal stage})
= Proposal