# Formulas

## 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