Formulas

Use these spreadsheet-like formulas to analyze your data

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