Canvas Docs
Search…
Supported formulas
See our full list of formulas and their syntax here.

# day(date)

Extract the day of the month as a number from 1 to 31 from a given date

### Arguments

1. 1.
`date` - the date to extract the day number of

# month(date)

Extract the month as a number from 1 to 12 from a given date

### Arguments

1. 1.
`date` - the date to extract the month number of

# year(date)

Extract the year number from the given date

### Arguments

1. 1.
`date` - the date to extract the year number of

# date(year, month, day)

Creates a date from the provided year, month, and day values

### Arguments

1. 1.
`year` - the year number of the date
2. 2.
`month` - the month number of the date from 1 to 12
3. 3.
`day` - the day number of the date from a to 31

## today()

Calculates today's date

# textcontains(expression_1, expression_2)

True if the first argument contains the second argument. Both arguments must be text.

### Arguments

1. 1.
`expression_1` - the string to search in
2. 2.
`expression_2` - the string to search for

# safediv(numerator, denominator)

Division, but returns 0 if the denominator is 0 instead of erroring.

### Arguments

1. 1.
`numerator` - the number to divide
2. 2.
`denominator` - the number to divide by

# nullif(condition, value)

Returns null if the value matches the condition, or the value otherwise.

### Arguments

1. 1.
`condition` - the condition to check
2. 2.
`value` - the value to return

# nullifzero(value)

Returns null if the value is 0 and the value otherwise. Useful for safe divison.

### Arguments

1. 1.
`value` - the value to test and return

# extractjson(column_id, path)

Parses the first argument as a JSON string and returns the value of the element pointed to by the path in the second argument. Accepts an arbitrary number of arguments

### Arguments

1. 1.
`column_id` - the name of the JSON column with the data that you want to extract.
2. 2.
`path` - (accepts one or more) the key(s) or indexes to look-up in the JSON document

# datediff(start_date, end_date, unit)

Calculates the number of years, months, days or hours between two dates.

### Arguments

1. 1.
`start_date` - the date to begin counting from
2. 2.
`end_date` - the date to finish count at
3. 3.
`unit` - either 'year', 'month', 'day' or 'hour'

# replace(original_value, from_value, to_value)

Replaces all occurrences of from_value with to_value in original_value

### Arguments

1. 1.
`original_value` - the string to perform the replace on
2. 2.
`from_value` - the value to be replaced
3. 3.
`to_value` - the value to replace with

# sql(expression)

A custom SQL statement

### Arguments

1. 1.
`expression` - the SQL expression

# if(condition, true_value, false_value)

If the first argument is true, return the second argument; otherwise, return the third argument

### Arguments

1. 1.
`condition` - the condition to be evalauted to true or false
2. 2.
`true_value` - value to return if the condition is true
3. 3.
`false_value` - value to return if the condition is false

# ifs(condition_1, condition_1_value, condition_2, condition_2_value)

Checks whether one or more conditions are met and returns the value that corresponds to the first TRUE condition. Accepts an arbitrary number of arguments.

### Arguments

1. 1.
`condition_1` - the first condition to be evalauted to true or false
2. 2.
`condition_1_value` - value to return if the first condition is true
3. 3.
`condition_2` - the second condition to be evalauted to true or false
4. 4.
`condition_2_value` - value to return if the second condition is true

# and(condition_1, condition_2)

Returns TRUE if all the input conditions in are true. Accepts an arbitrary number of arguments.

### Arguments

1. 1.
`condition_1` - the first condition to be evalauted to true or false
2. 2.
`condition_2` - the second condition to be evalauted to true or false

# or(condition_1, condition_2)

Returns TRUE if any of the input conditions in are true. Accepts an arbitrary number of arguments.

### Arguments

1. 1.
`condition_1` - the first condition to be evalauted to true or false
2. 2.
`condition_2` - the second condition to be evalauted to true or false

# not(condition)

Returns TRUE if the given conditional is FALSE.

### Arguments

1. 1.
`condition` - the condition to be evalauted to true or false

# round(number, scale)

Rounds the given number to the given scale.

### Arguments

1. 1.
`number` - the number to be rounded
2. 2.
`scale` - the number of decimal points to round to

# roundup(number)

Rounds the given number up to the nearest integer.

### Arguments

1. 1.
`number` - the number to be rounded

# rounddown(number)

Rounds the given number down to the nearest integer.

### Arguments

1. 1.
`number` - the number to be rounded

# concat(string_1, string_2)

Concatenates the text from the provided arguments into a single string. Accepts an arbitrary number of arguments.

### Arguments

1. 1.
`string_1` - first string to concatenate
2. 2.
`string_2` - concatenated onto the end of string 1

# datevalue(date)

Converts a date-like text field into a date

### Arguments

1. 1.
`date` - a date-like text field

# sum(column)

Returns the sum of a column.

### Arguments

1. 1.
`column` - the column to sum over

# average(column)

Returns the average of a column.

### Arguments

1. 1.
`column` - the column to average over

# countunique(column)

Counts the number of unique values in a column

### Arguments

1. 1.
`column` - the column to count uniques

Returns a value from a row below the current row, offset by a number of rows (by default 1)

### Arguments

1. 1.
`column` - the column to be returned
2. 2.
`offset` - the number of rows to offset by (defaults to 1)

# lag(column, offset)

Returns a value from the a previous row, offset by a number of rows (by default 1)

### Arguments

1. 1.
`column` - the column to be returned
2. 2.
`offset` - the number of rows to offset by (defaults to 1)

# sumwindow(column)

Returns a sum of values from a column in a pivot

### Arguments

1. 1.
`column` - the column to be summed over
day(date)
month(date)
year(date)
date(year, month, day)
today()
textcontains(expression_1, expression_2)
safediv(numerator, denominator)
nullif(condition, value)
nullifzero(value)
extractjson(column_id, path)
datediff(start_date, end_date, unit)
replace(original_value, from_value, to_value)
sql(expression)
if(condition, true_value, false_value)
ifs(condition_1, condition_1_value, condition_2, condition_2_value)
and(condition_1, condition_2)
or(condition_1, condition_2)
not(condition)
round(number, scale)
roundup(number)
rounddown(number)
concat(string_1, string_2)
datevalue(date)
sum(column)
average(column)
countunique(column)