Supported formulas
Introduction
Setup
Connect your data
Snowflake
BigQuery
Redshift
Postgres
App connectors
Using Canvas
Formulas
Team permissions
API
Canvas
Events
Login
Embeds
Introduction
Scopes
Public access
PowerPoint
Security
Data security
Bug bounty program

Supported formulas

day(date)

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

Arguments

  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. date - the date to extract the month number of

year(date)

Extract the year number from the given date

Arguments

  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. year - the year number of the date
  2. month - the month number of the date from 1 to 12
  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. expression_1 - the string to search in
  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. numerator - the number to divide
  2. denominator - the number to divide by

nullif(condition, value)

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

Arguments

  1. condition - the condition to check
  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. 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. column_id - the name of the JSON column with the data that you want to extract.
  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. start_date - the date to begin counting from
  2. end_date - the date to finish count at
  3. unit - either 'year', 'month', 'day', 'hour', or 'minute'

dateadd(unit, amount, start_date)

Adds a number of date units to a date.

Arguments

  1. unit - either 'year', 'month', 'day', 'hour', or 'minute'
  2. amount - the amount of date units to add
  3. start_date - the date to modify

replace(original_value, from_value, to_value)

Replaces all occurrences of from_value with to_value in original_value

Arguments

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

sql(expression)

A custom SQL statement

Arguments

  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. condition - the condition to be evalauted to true or false
  2. true_value - value to return if the condition is true
  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. condition_1 - the first condition to be evalauted to true or false
  2. condition_1_value - value to return if the first condition is true
  3. condition_2 - the second condition to be evalauted to true or false
  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. condition_1 - the first condition to be evalauted to true or false
  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. condition_1 - the first condition to be evalauted to true or false
  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. condition - the condition to be evalauted to true or false

round(number, scale)

Rounds the given number to the given scale.

Arguments

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

roundup(number)

Rounds the given number up to the nearest integer.

Arguments

  1. number - the number to be rounded

rounddown(number)

Rounds the given number down to the nearest integer.

Arguments

  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. string_1 - first string to concatenate
  2. string_2 - concatenated onto the end of string 1

datevalue(date)

Converts a date-like text field into a date

Arguments

  1. date - a date-like text field

sum(column)

Returns the sum of a column.

Arguments

  1. column - the column to sum over

average(column)

Returns the average of a column.

Arguments

  1. column - the column to average over

countunique(column)

Counts the number of unique values in a column

Arguments

  1. column - the column to count uniques

lead(column, offset, order_by, partition_by)

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

Arguments

  1. column - the column to be returned
  2. offset - the number of rows to offset by (defaults to 1)
  3. order_by - the order by which the following row is determined
  4. partition_by - pick following row only from rows matching this partition

lag(column, offset, order_by, partition_by)

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

Arguments

  1. column - the column to be returned
  2. offset - the number of rows to offset by (defaults to 1)
  3. order_by - the order by which the previous row is determined
  4. partition_by - pick previous row only from rows matching this partition

previous(column, offset, order_by, partition_by)

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

Arguments

  1. column - the column to be returned
  2. offset - the number of rows to offset by (defaults to 1)
  3. order_by - the order by which the previous row is determined
  4. partition_by - pick previous row only from rows matching this partition

next(column, offset, order_by, partition_by)

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

Arguments

  1. column - the column to be returned
  2. offset - the number of rows to offset by (defaults to 1)
  3. order_by - the order by which the following row is determined
  4. partition_by - pick following row only from rows matching this partition

sumwindow(column)

Returns a sum of values from a column in a pivot

Arguments

  1. column - the column to be summed over
Company
Legal
© 2024 Infinite Canvas Inc.
Twitter logo
LinkedIn logo
Spotify logo