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
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
date
- the date to extract the month number of
year(date)
Extract the year number from the given date
Arguments
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
year
- the year number of the datemonth
- the month number of the date from 1 to 12day
- 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
expression_1
- the string to search inexpression_2
- the string to search for
safediv(numerator, denominator)
Division, but returns 0 if the denominator is 0 instead of erroring.
Arguments
numerator
- the number to dividedenominator
- the number to divide by
nullif(condition, value)
Returns null if the value matches the condition, or the value otherwise.
Arguments
condition
- the condition to checkvalue
- the value to return
nullifzero(value)
Returns null if the value is 0 and the value otherwise. Useful for safe divison.
Arguments
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
column_id
- the name of the JSON column with the data that you want to extract.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
start_date
- the date to begin counting fromend_date
- the date to finish count atunit
- either 'year', 'month', 'day', 'hour', or 'minute'
dateadd(unit, amount, start_date)
Adds a number of date units to a date.
Arguments
unit
- either 'year', 'month', 'day', 'hour', or 'minute'amount
- the amount of date units to addstart_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
original_value
- the string to perform the replace onfrom_value
- the value to be replacedto_value
- the value to replace with
sql(expression)
A custom SQL statement
Arguments
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
condition
- the condition to be evalauted to true or falsetrue_value
- value to return if the condition is truefalse_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
condition_1
- the first condition to be evalauted to true or falsecondition_1_value
- value to return if the first condition is truecondition_2
- the second condition to be evalauted to true or falsecondition_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
condition_1
- the first condition to be evalauted to true or falsecondition_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
condition_1
- the first condition to be evalauted to true or falsecondition_2
- the second condition to be evalauted to true or false
not(condition)
Returns TRUE if the given conditional is FALSE.
Arguments
condition
- the condition to be evalauted to true or false
round(number, scale)
Rounds the given number to the given scale.
Arguments
number
- the number to be roundedscale
- the number of decimal points to round to
roundup(number)
Rounds the given number up to the nearest integer.
Arguments
number
- the number to be rounded
rounddown(number)
Rounds the given number down to the nearest integer.
Arguments
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
string_1
- first string to concatenatestring_2
- concatenated onto the end of string 1
datevalue(date)
Converts a date-like text field into a date
Arguments
date
- a date-like text field
sum(column)
Returns the sum of a column.
Arguments
column
- the column to sum over
average(column)
Returns the average of a column.
Arguments
column
- the column to average over
countunique(column)
Counts the number of unique values in a column
Arguments
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
column
- the column to be returnedoffset
- the number of rows to offset by (defaults to 1)order_by
- the order by which the following row is determinedpartition_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
column
- the column to be returnedoffset
- the number of rows to offset by (defaults to 1)order_by
- the order by which the previous row is determinedpartition_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
column
- the column to be returnedoffset
- the number of rows to offset by (defaults to 1)order_by
- the order by which the previous row is determinedpartition_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
column
- the column to be returnedoffset
- the number of rows to offset by (defaults to 1)order_by
- the order by which the following row is determinedpartition_by
- pick following row only from rows matching this partition
sumwindow(column)
Returns a sum of values from a column in a pivot
Arguments
column
- the column to be summed over
Last updated