Let’s Automate

04.13 Function reference

Every function a formula can use: what it does, each argument, and an example.

Every formula (SQL formula, UI formula, a dataset’s Custom formula) uses these functions. The editor shows the same help while you type: put the cursor inside a function’s brackets and it shows each argument, with the one you are on highlighted.

  • Text in a formula goes in straight quotes, double or single: status = "Paid" or status = 'Paid'. Curly quotes (‘ ’, “ ”) are not accepted.
  • A column whose name has spaces goes in square brackets: [Unit price] * qty.
  • Text is compared ignoring capitals: "PAID" matches paid.
  • A blank counts as 0 in arithmetic.
  • An SQL formula offers only the functions your database can run; the editor’s Functions list shows exactly those.

Math

round(value, places)

Round to a number of decimal places.

Argument What it is Example
value The number to round amount
places Optional. Decimal places to keep; 0 when left out 2

Example: round(amount, 2)

abs(value)

Distance from zero, ignoring sign.

Argument What it is Example
value A number variance

Example: abs(variance)

ceil(value)

Round up to a whole number.

Argument What it is Example
value A number days

Example: ceil(days)

floor(value)

Round down to a whole number.

Argument What it is Example
value A number days

Example: floor(days)

sqrt(value)

Square root.

Argument What it is Example
value A number, 0 or more area

Example: sqrt(area)

min(value …)

Smallest of the values given — not across rows.

Argument What it is Example
value … Two or more values to compare, on the same row price, cap

Example: min(price, cap)

max(value …)

Largest of the values given — not across rows.

Argument What it is Example
value … Two or more values to compare, on the same row price, floor_price

Example: max(price, floor_price)

trunc(value)

Drop the decimals, toward zero.

Argument What it is Example
value A number hours

Example: trunc(hours)

exp(value)

e raised to the value.

Argument What it is Example
value The power to raise e to rate

Example: exp(rate)

ln(value)

Natural logarithm.

Argument What it is Example
value A number above 0 growth

Example: ln(growth)

log(value)

Natural logarithm — the same as ln, not base 10.

Argument What it is Example
value A number above 0 growth

Example: log(growth)

log10(value)

Base-10 logarithm.

Argument What it is Example
value A number above 0 size

Example: log10(size)

log2(value)

Base-2 logarithm.

Argument What it is Example
value A number above 0 size

Example: log2(size)

cbrt(value)

Cube root.

Argument What it is Example
value A number volume

Example: cbrt(volume)

pow(value, exponent)

Raise to a power.

Argument What it is Example
value The number to raise rate
exponent The power to raise it to years

Example: pow(rate, years)

Text

upper(text)

Convert to upper case.

Argument What it is Example
text The text to change city

Example: upper(city)

lower(text)

Convert to lower case.

Argument What it is Example
text The text to change email

Example: lower(email)

trim(text)

Remove leading and trailing spaces.

Argument What it is Example
text The text to clean name

Example: trim(name)

concat(value …)

Join values into one piece of text.

Argument What it is Example
value … The pieces to join, in order — columns or “text in quotes” first, " ", last

Example: concat(first, " ", last)

substring(text, start, count)

Part of a value, counting from 1.

Argument What it is Example
text The text to take from code
start Where to start; the first character is 1 1
count Optional. How many characters; to the end when left out 3

Example: substring(code, 1, 3)

left(text, count)

First few characters.

Argument What it is Example
text The text to take from deptCode
count How many characters from the start 3

Example: left(deptCode, 3)

right(text, count)

Last few characters.

Argument What it is Example
text The text to take from invoice
count How many characters from the end 4

Example: right(invoice, 4)

replace(text, find, replace with)

Swap one piece of text for another.

Argument What it is Example
text The text to change phone
find What to look for, in quotes "-"
replace with What to put instead, in quotes (“” removes it) ""

Example: replace(phone, "-", "")

contains(text, find)

True when the text contains it.

Argument What it is Example
text The text to look in notes
find What to look for, in quotes; capitals ignored "urgent"

Example: contains(notes, "urgent")

startsWith(text, find)

True when the text begins with it.

Argument What it is Example
text The text to look at sku
find What it should begin with, in quotes; capitals ignored "IN"

Example: startsWith(sku, "IN")

endsWith(text, find)

True when the text ends with it.

Argument What it is Example
text The text to look at file
find What it should end with, in quotes; capitals ignored ".pdf"

Example: endsWith(file, ".pdf")

length(text)

How many characters.

Argument What it is Example
text The text to count name

Example: length(name)

Date

year(date)

The year, as a number.

Argument What it is Example
date A date column order_date

Example: year(order_date)

month(date)

The month, 1–12.

Argument What it is Example
date A date column order_date

Example: month(order_date)

day(date)

Day of the month.

Argument What it is Example
date A date column order_date

Example: day(order_date)

quarter(date)

The quarter, 1–4.

Argument What it is Example
date A date column order_date

Example: quarter(order_date)

weekday(date)

The day of the week by name.

Argument What it is Example
date A date column order_date

Example: weekday(order_date)

datediff(from, to)

Whole days between two dates.

Argument What it is Example
from The earlier date ordered
to The later date; days from “from” to this shipped

Example: datediff(ordered, shipped)

dateadd(date, days)

Move a date by a number of days.

Argument What it is Example
date The date to move order_date
days How many days to add; negative goes back 30

Example: dateadd(order_date, 30)

monthname(date)

The month by name. Reads well, but sorts alphabetically — group by monthkey instead.

Argument What it is Example
date A date column, or a month number 1–12 order_date

Example: monthname(order_date)

monthkey(date)

The month as 2024-06 — sorts chronologically, so group or pivot by this.

Argument What it is Example
date A date column order_date

Example: monthkey(order_date)

quarterkey(date)

The quarter as 2024-Q2 — sorts chronologically.

Argument What it is Example
date A date column order_date

Example: quarterkey(order_date)

daykey(date)

The day as 2024-06-17 — sorts chronologically.

Argument What it is Example
date A date column order_date

Example: daykey(order_date)

today()

Today’s date, as 2024-06-17.

Example: datediff(due_date, today())

yesterday()

Yesterday’s date, as 2024-06-16.

Example: order_date = yesterday()

ytd(date)

True when the date is in this calendar year, up to today. Pair it with sumif.

Argument What it is Example
date The date column that decides the year order_date

Example: ytd(order_date)

fytd(date)

True when the date is in this financial year, up to today. Pair it with sumif.

Argument What it is Example
date The date column that decides the financial year order_date

Example: fytd(order_date)

Logic

if(condition, then, otherwise)

Choose between two values. Nests freely.

Argument What it is Example
condition A test that is true or false qty > 10
then The result when it is true "bulk"
otherwise The result when it is false "single"

Example: if(qty > 10, "bulk", "single")

coalesce(value …)

First value that is not empty.

Argument What it is Example
value … Values to try in order; the first that is not empty wins nickname, name

Example: coalesce(nickname, name)

ifnull(value, fallback)

Use a fallback when the value is empty.

Argument What it is Example
value The value to use discount
fallback What to use when it is empty 0

Example: ifnull(discount, 0)

isnull(value)

True when the value is empty.

Argument What it is Example
value The value to test closed_date

Example: isnull(closed_date)

between(value, low, high)

True when the value is from low to high, both included.

Argument What it is Example
value The value to test score
low The lowest allowed, included 600
high The highest allowed, included 750

Example: between(score, 600, 750)

Aggregate

A total over the rows of each report row (group). Using one makes the formula a group formula. Inside a total’s condition, $thisrow.column is the current row’s own value: sumif(month = $thisrow.month, revenue).

sum(value)

Total across the rows in this group.

Argument What it is Example
value What to add up for each row of the group — a column or a calculation amount

Example: sum(amount)

sumif(condition, value)

Total across the rows that match.

Argument What it is Example
condition Which rows to add: a test that is true or false for each row status = "Paid"
value What to add up for those rows — a column or a calculation amount

Example: sumif(status = "Refunded", amount)

countif(condition)

How many rows match.

Argument What it is Example
condition Which rows to count: a test that is true or false for each row qty > 10

Example: countif(qty > 10)

countifunique(condition, value)

How many different values among the rows that match.

Argument What it is Example
condition Which rows to look at: a test that is true or false for each row status = "Paid"
value What to count the different values of customer_id

Example: countifunique(status = "Paid", customer_id)

Next: 05.1 What a dashboard is

Last updated 2026-10-26T00:00:00.000Z