Appearance
Formula reference
Logic functions
and
Returns true if all the given values are true, false otherwise.
Arguments
value
Expression that represents a logical value
and(Boolean, [Boolean, ...]): Boolean
or
Returns true if at least one of the given values is true, false otherwise.
Arguments
value
Expression that represents a logical value
or(Boolean, [Boolean, ...]): Boolean
not
Returns true if the given value is false, false otherwise.
Arguments
value
Expression that represents a logical value
not(Boolean): Boolean
eq
Equality check. Returns true if two given values are equal
Arguments
value1
First valuevalue2
Value to test against value1 for equality
Supported signatures
eq(String | Enum, String | Enum): Boolean
eq(Number | Float | Integer, Number | Float | Integer): Boolean
eq(Boolean, Boolean): Boolean
eq(Date, Date): Boolean
neq
Non-equality check. Returns false if two given values are equal, true otherwise.
Arguments
value1
First valuevalue2
Value to test against value1 for equality
Supported signatures
neq(String | Enum, String | Enum): Boolean
neq(Number | Float | Integer, Number | Float | Integer): Boolean
neq(Boolean, Boolean): Boolean
neq(Date, Date): Boolean
gt
Returns true if the first value is greater than the second, false otherwise.
Arguments
value1
Value to test being greater than value2value2
Second value
Supported signatures
gt(String | Enum, String | Enum): Boolean
gt(Number | Float | Integer, Number | Float | Integer): Boolean
gt(Boolean, Boolean): Boolean
gt(Date, Date): Boolean
gte
Returns true if the first value is greater than or equal to the second, false otherwise.
Arguments
value1
Value to test being greater or equal than value2value2
Second value
Supported signatures
gte(String | Enum, String | Enum): Boolean
gte(Number | Float | Integer, Number | Float | Integer): Boolean
gte(Boolean, Boolean): Boolean
gte(Date, Date): Boolean
lt
Returns true if the first value is less than the second, false otherwise.
Arguments
value1
Value to test being less than value2value2
Second value
Supported signatures
lt(String | Enum, String | Enum): Boolean
lt(Number | Float | Integer, Number | Float | Integer): Boolean
lt(Boolean, Boolean): Boolean
lt(Date, Date): Boolean
lte
Returns true if the first value is less than or equal to the second, false otherwise.
Arguments
value1
Value to test being less or equal than value2value2
Second value
Supported signatures
lte(String | Enum, String | Enum): Boolean
lte(Number | Float | Integer, Number | Float | Integer): Boolean
lte(Boolean, Boolean): Boolean
lte(Date, Date): Boolean
Control flow
case
Returns the first value that matches its preceding condition. An optional value is returned if it is provided and none of the conditions match.
Arguments
condition
Condition to matchvalue
Value to return if the preceding condition is truedefault
(Optional) Value to return if no conditions match
Supported signatures
case(Boolean, String | Enum, [Boolean, String | Enum, ...], String?): String
case(Boolean, Number | Float | Integer, [Boolean, Number | Float | Integer, ...], Number | Float | Integer?): Number
case(Boolean, Float, [Boolean, Float, ...], Float?): Float
case(Boolean, Integer, [Boolean, Integer, ...], Integer?): Integer
case(Boolean, Date, [Boolean, Date, ...], Date?): Date
case(Boolean, Interval, [Boolean, Interval, ...], Interval?): Interval
case(Boolean, Boolean, [Boolean, Boolean, ...], Boolean?): Boolean
Mathematical functions
add
Returns the sum of given values.
Arguments
value
Value to sum together with previous value
Supported signatures
add(Number, [Number, ...]): Number
add(Float, [Float, ...]): Float
add(Integer, [Integer, ...]): Integer
add(Number | Float | Integer, [Number | Float | Integer, ...]): Number
add(Interval, [Interval, ...]): Interval
add(Date, Interval): Date
add(Interval, Date): Date
sub
Returns the subtraction of given values.
Arguments
value
Value to subtract from previous value
Supported signatures
sub(Number, [Number, ...]): Number
sub(Float, [Float, ...]): Float
sub(Integer, [Integer, ...]): Integer
sub(Number | Float | Integer, [Number | Float | Integer, ...]): Number
sub(Date, Date): Interval
sub(Date, Interval): Date
sub(Interval, Interval): Interval
mul
Returns the multiplication of given values.
Arguments
value
Value to multiply to previous value
Supported signatures
mul(Number, [Number, ...]): Number
mul(Float, [Float, ...]): Float
mul(Integer, [Integer, ...]): Integer
mul(Number | Float | Integer, [Number | Float | Integer, ...]): Number
mul(Interval, Number): Interval
mul(Number, Interval): Interval
div
Returns the division of given values.
Arguments
dividend
The value to be divideddivisor
The number to divide by
Supported signatures
div(Float | Integer, Float | Integer): Float
div(Number | Float | Integer, Number | Float | Integer): Number
div(Interval, Number): Interval
max
Returns the maximum value from the given values.
Arguments
value
Value to consider as maximum value
Supported signatures
max(Number, [Number, ...]): Number
max(Float, [Float, ...]): Float
max(Integer, [Integer, ...]): Integer
max(Number | Float | Integer, [Number | Float | Integer, ...]): Number
max(Date, [Date, ...]): Date
max(Interval, [Interval, ...]): Interval
min
Returns the minimum value from the given values.
Arguments
value
Value to consider as minimum value
Supported signatures
min(Number, [Number, ...]): Number
min(Float, [Float, ...]): Float
min(Integer, [Integer, ...]): Integer
min(Number | Float | Integer, [Number | Float | Integer, ...]): Number
min(Date, [Date, ...]): Date
min(Interval, [Interval, ...]): Interval
ceil
Returns the smallest integer greater than or equal to the given value.
Arguments
value
Value to round up
ceil(Number | Float | Integer): Number
floor
Returns the largest integer less than or equal to the given value.
Arguments
value
Value to round down
floor(Number | Float | Integer): Number
round
Returns the value rounded to the nearest integer.
Arguments
value
Value to round to the nearest integerprecision
An integer specifying the number of significant digits
Supported signatures
round(Number | Float | Integer, Number): Number
round(Number | Float | Integer): Number
String functions
contains
Returns true if the first value contains the second value (case-insensitive), false otherwise.
Arguments
search_string
String to search for the presence of lookup stringlookup_string
String to search for within the search string
Supported signatures
contains(String, String): Boolean
contains(Enum, String): Boolean
split_part
Splits the given string by the given delimiter and returns the part at the given position. The position is 1-based. If the position is greater than the number of parts, a null value is returned.
Arguments
string
String to splitdelimiter
Delimiter to split the string byposition
Position of the part to return
split_part(String | Enum, String, Number): String
concat
Concatenates given values into a single string.
Arguments
value
Value to concatenate
concat(String | Number | Float | Integer | Enum, [String | Number | Float | Integer | Enum, ...]): String
len
Returns the length of the string from the given value.
Arguments
string
String to get the length of
len(String): Integer
Date functions
date
Returns the given value as a Date.
Arguments
value
Value to convert to a date
date(String | Date): Date
now
Returns the current date and time.
Arguments
now(): Date
year
Extracts the year from the given date value.
Arguments
date
Date value to extract the year from
year(Date): Number
quarter
Extracts the quarter from the given date value.
Arguments
date
Date value to extract the quarter from
quarter(Date): Number
month
Extracts the month from the given date value.
Arguments
date
Date value to extract the month from
month(Date): Number
week
Extracts the week number from the given date value.
Arguments
date
Date value to extract the week number from
week(Date): Number
day
Extracts the day from the given date value.
Arguments
date
Date value to extract the day from
day(Date): Number
hour
Extracts the hour from the given date value.
Arguments
date
Date value to extract the hour from
hour(Date): Number
minute
Extracts the minute from the given date value.
Arguments
date
Date value to extract the minute from
minute(Date): Number
second
Extracts the second from the given date value.
Arguments
date
Date value to extract the second from
second(Date): Number
day_of_week
Extracts the day of the week from the given date value.
Arguments
date
Date value to extract the day of the week from
day_of_week(Date): Number
Date utility functions
interval
Creates an interval value of the given duration.
Arguments
count
How many periods in the intervalperiod
Length of one period (allowed values:'year'
,'quarter'
,'month'
,'week'
,'day'
,'hour'
,'minute'
,'second'
,'millisecond'
)
interval(Number, String): Interval
extract
Extracts given date part from the given interval.
Arguments
interval
Interval valuefield
Date part to extract (allowed values:'year'
,'quarter'
,'month'
,'week'
,'day'
,'hour'
,'minute'
,'second'
,'millisecond'
)
extract(Interval, String): Number
date_diff
Calculates the difference between two dates in the specified unit.
Arguments
date1
First date valuedate2
Second date valueunit
Unit of the result (allowed values:'year'
,'quarter'
,'month'
,'week'
,'day'
,'hour'
,'minute'
,'second'
)
date_diff(Date, Date, String): Number
start_of_year
Returns the given date value truncated to the start of the year.
Arguments
date
Date value to truncate
start_of_year(Date): Date
end_of_year
Return the given date value truncated to the end of the year / start of next year.
Arguments
date
Date value to truncate
end_of_year(Date): Date
start_of_quarter
Returns the given date value truncated to the start of the quarter.
Arguments
date
Date value to truncate
start_of_quarter(Date): Date
end_of_quarter
Return the given date value truncated to the end of the quarter / start of next quarter.
Arguments
date
Date value to truncate
end_of_quarter(Date): Date
start_of_month
Returns the given date value truncated to the start of the month.
Arguments
date
Date value to truncate
start_of_month(Date): Date
end_of_month
Returns the given date value truncated to the end of the month / start of next month.
Arguments
date
Date value to truncate
end_of_month(Date): Date
start_of_week
Returns the given date value truncated to the start of the week.
Arguments
date
Date value to truncate
start_of_week(Date): Date
end_of_week
Returns the given date value truncated to the end of the week / start of next week.
Arguments
date
Date value to truncate
end_of_week(Date): Date
start_of_day
Returns the given date value truncated to the start of the day.
Arguments
date
Date value to truncate
start_of_day(Date): Date
end_of_day
Returns the given date value truncated to the end of the day / start of next day.
Arguments
date
Date value to truncate
end_of_day(Date): Date
start_of_hour
Returns the given date value truncated to the start of the hour.
Arguments
date
Date value to truncate
start_of_hour(Date): Date
end_of_hour
Returns the given date value truncated to the end of the hour / start of next hour.
Arguments
date
Date value to truncate
end_of_hour(Date): Date
start_of_minute
Returns the given date value truncated to the start of the minute.
Arguments
date
Date value to truncate
start_of_minute(Date): Date
end_of_minute
Returns the given date value truncated to the end of the minute / start of next minute.
Arguments
date
Date value to truncate
end_of_minute(Date): Date
JSON functions
json_get_text
Returns the value of the given key from a json object as a string.
Arguments
object
JSON objectkey
Key to get the value of
json_get_text(Object, String): String
json_get_number
Returns the value of the given key from a json object as a number.
Arguments
object
JSON objectkey
Key to get the value of
json_get_number(Object, String): Number
json_get_integer
Returns the value of the given key from a json object as an integer.
Arguments
object
JSON objectkey
Key to get the value of
json_get_integer(Object, String): Integer
json_get_float
Returns the value of the given key from a json object as a float.
Arguments
object
JSON objectkey
Key to get the value of
json_get_float(Object, String): Float
json_get_timestamptz
Returns the value of the given key from a json object as a date.
Arguments
object
JSON objectkey
Key to get the value of
json_get_timestamptz(Object, String): Date
json_get_boolean
Returns the value of the given key from a json object as a boolean.
Arguments
object
JSON objectkey
Key to get the value of
json_get_boolean(Object, String): Boolean
Type conversions
int
Returns the given value as an integer.
Arguments
value
Value to convert to an integer
int(Boolean | String | Enum | Number | Float | Integer): Integer
float
Returns the given value as a floating point number.
Arguments
value
Value to convert to a float
float(String | Enum | Number | Float | Integer): Float
str
Returns the given value as a string.
Arguments
value
Value to convert to a string
str(Boolean | String | Enum | Number | Float | Integer | Date | Object | Interval): String
bool
Returns the given value as a boolean.
Arguments
value
Value to convert to a boolean
bool(Boolean | Number | Integer): Boolean
Window functions
sum
Returns the sum of the given column or range. Range nomenclature: unbounded: null, current row: 0, previous row: -1, next row: 1.
Arguments
field
Field to aggregatesort_field
(Optional) Field to sort bysort_direction
(Optional) Sort direction ("asc"/"desc")range_start
(Optional) Start index of range to sum over, relative to the current rowrange_end
(Optional) End index of range to sum over, relative to the current rowgroup
(Optional) Field to group by
Supported signatures
sum(Number | Float | Integer): Number
sum(Number | Float | Integer, String | Enum | Date | Number | Integer | Float | Boolean, String, Number, Number): Number
sum(Number | Float | Integer, String | Enum | Date | Number | Integer | Float | Boolean, String, Number, Number, String | Enum | Date | Number | Integer | Float | Boolean): Number
sum(Number | Float | Integer, String | Enum | Date | Number | Integer | Float | Boolean): Number
count
Returns the count of the given column or range. Range nomenclature: unbounded: null, current row: 0, previous row: -1, next row: 1.
Arguments
sort_field
(Optional) Field to sort bysort_direction
(Optional) Sort direction ("asc"/"desc")range_start
(Optional) Start index of range to count from, relative to the current rowrange_end
(Optional) End index of range to count to, relative to the current rowgroup
(Optional) Field to group by
Supported signatures
count(): Integer
count(String | Enum | Date | Number | Integer | Float | Boolean): Integer
count(String | Enum | Date | Number | Integer | Float | Boolean, String, Number, Number): Integer
count(String | Enum | Date | Number | Integer | Float | Boolean, String, Number, Number, String | Enum | Date | Number | Integer | Float | Boolean): Integer
avg
Returns the average of the given values. Range nomenclature: unbounded: null, current row: 0, previous row: -1, next row: 1.
Arguments
field
Field to aggregatesort_field
(Optional) Field to sort bysort_direction
(Optional) Sort direction ("asc"/"desc")range_start
(Optional) Start index of range to average over, relative to the current rowrange_end
(Optional) End index of range to average over, relative to the current rowgroup
(Optional) Field to group by
Supported signatures
avg(Number | Float | Integer): Number
avg(Number | Float | Integer, String | Enum | Date | Number | Integer | Float | Boolean, String, Number, Number): Number
avg(Number | Float | Integer, String | Enum | Date | Number | Integer | Float | Boolean, String, Number, Number, String | Enum | Date | Number | Integer | Float | Boolean): Number
avg(Number | Float | Integer, String | Enum | Date | Number | Integer | Float | Boolean): Number
range_min
Returns the minimum value of the given column or range. Range nomenclature: unbounded: null, current row:0, previous row: -1, next row: 1.
Arguments
field
Field to aggregatesort_field
(Optional) Field to sort bysort_direction
(Optional) Sort direction ("asc"/"desc")range_start
(Optional) Start index of range to find the minimum value over, relative to the current rowrange_end
(Optional) End index of range to find the minimum value over, relative to the current rowgroup
(Optional) Field to group by
Supported signatures
range_min(String | Enum): String
range_min(Date): Date
range_min(Number | Float | Integer): Number
range_min(String | Enum, String | Enum | Date | Number | Integer | Float | Boolean, String, Number, Number): String
range_min(Date, String | Enum | Date | Number | Integer | Float | Boolean, String, Number, Number): Date
range_min(Number | Float | Integer, String | Enum | Date | Number | Integer | Float | Boolean, String, Number, Number): Number
range_min(String | Enum, String | Enum | Date | Number | Integer | Float | Boolean, String, Number, Number, String | Enum | Date | Number | Integer | Float | Boolean): String
range_min(Date, String | Enum | Date | Number | Integer | Float | Boolean, String, Number, Number, String | Enum | Date | Number | Integer | Float | Boolean): Date
range_min(Number | Float | Integer, String | Enum | Date | Number | Integer | Float | Boolean, String, Number, Number, String | Enum | Date | Number | Integer | Float | Boolean): Number
range_min(String | Enum, String | Enum | Date | Number | Integer | Float | Boolean): String
range_min(Date, String | Enum | Date | Number | Integer | Float | Boolean): Date
range_min(Number | Float | Integer, String | Enum | Date | Number | Integer | Float | Boolean): Number
range_max
Returns the maximum value of the given column or range. Range nomenclature: unbounded: null, current row:0, previous row: -1, next row: 1.
Arguments
field
Field to aggregatesort_field
(Optional) Field to sort bysort_direction
(Optional) Sort direction ("asc"/"desc")range_start
(Optional) Start index of range to find the minimum value over, relative to the current rowrange_end
(Optional) End index of range to find the minimum value over, relative to the current rowgroup
(Optional) Field to group by
Supported signatures
range_max(String | Enum): String
range_max(Date): Date
range_max(Number | Float | Integer): Number
range_max(String | Enum, String | Enum | Date | Number | Integer | Float | Boolean, String, Number, Number): String
range_max(Date, String | Enum | Date | Number | Integer | Float | Boolean, String, Number, Number): Date
range_max(Number | Float | Integer, String | Enum | Date | Number | Integer | Float | Boolean, String, Number, Number): Number
range_max(String | Enum, String | Enum | Date | Number | Integer | Float | Boolean, String, Number, Number, String | Enum | Date | Number | Integer | Float | Boolean): String
range_max(Date, String | Enum | Date | Number | Integer | Float | Boolean, String, Number, Number, String | Enum | Date | Number | Integer | Float | Boolean): Date
range_max(Number | Float | Integer, String | Enum | Date | Number | Integer | Float | Boolean, String, Number, Number, String | Enum | Date | Number | Integer | Float | Boolean): Number
range_max(String | Enum, String | Enum | Date | Number | Integer | Float | Boolean): String
range_max(Date, String | Enum | Date | Number | Integer | Float | Boolean): Date
range_max(Number | Float | Integer, String | Enum | Date | Number | Integer | Float | Boolean): Number
percentage
Returns the percentage (between 0 and 1) of the value in a row relative to the sum of the value in all rows. The optional second argument can be used to break up the rows into groups when calculating the percentage.
Arguments
value
Value to calculate the percentage ofgroup
(Optional) Value by which to group the rows during calculation
Supported signatures
percentage(Number): Number
percentage(Number, String | Enum | Boolean | Date): Number
percentage(Float): Float
percentage(Float, String | Enum | Boolean | Date): Float
percentage(Integer): Integer
percentage(Integer, String | Enum | Boolean | Date): Integer
offset
Returns the value of the given property offset from the current row by the given amount.
Arguments
field
Field to offsetoffset
Offset index (<0 will result in previous values, e.g. -2)sort_field
Field to sort bysort_direction
Sort direction ("asc"/"desc")group
(Optional) Field to group by
Supported signatures
offset(String, Number, String | Enum | Date | Number | Integer | Float | Boolean, String?): String
offset(Enum, Number, String | Enum | Date | Number | Integer | Float | Boolean, String?): Enum
offset(Number, Number, String | Enum | Date | Number | Integer | Float | Boolean, String?): Number
offset(Integer, Number, String | Enum | Date | Number | Integer | Float | Boolean, String?): Integer
offset(Float, Number, String | Enum | Date | Number | Integer | Float | Boolean, String?): Float
offset(Boolean, Number, String | Enum | Date | Number | Integer | Float | Boolean, String?): Boolean
offset(Date, Number, String | Enum | Date | Number | Integer | Float | Boolean, String?): Date
offset(String, Number, String | Enum | Date | Number | Integer | Float | Boolean, String, String | Enum | Date | Number | Integer | Float | Boolean): String
offset(Enum, Number, String | Enum | Date | Number | Integer | Float | Boolean, String, String | Enum | Date | Number | Integer | Float | Boolean): Enum
offset(Number, Number, String | Enum | Date | Number | Integer | Float | Boolean, String, String | Enum | Date | Number | Integer | Float | Boolean): Number
offset(Integer, Number, String | Enum | Date | Number | Integer | Float | Boolean, String, String | Enum | Date | Number | Integer | Float | Boolean): Integer
offset(Float, Number, String | Enum | Date | Number | Integer | Float | Boolean, String, String | Enum | Date | Number | Integer | Float | Boolean): Float
offset(Boolean, Number, String | Enum | Date | Number | Integer | Float | Boolean, String, String | Enum | Date | Number | Integer | Float | Boolean): Boolean
offset(Date, Number, String | Enum | Date | Number | Integer | Float | Boolean, String, String | Enum | Date | Number | Integer | Float | Boolean): Date
row_number
Returns the row number of the row in the current group.
Arguments
sort_field
(Optional) Field to sort bysort_direction
(Optional) Sort direction ("asc"/"desc")group
(Optional) Field to group by
Supported signatures
row_number(): Integer
row_number(String | Enum | Date | Number | Integer | Float | Boolean): Integer
row_number(String | Enum | Date | Number | Integer | Float | Boolean, String): Integer
row_number(String | Enum | Date | Number | Integer | Float | Boolean, String, String | Enum | Date | Number | Integer | Float | Boolean): Integer
Utility functions
prop
Returns the value of the given property from the current row.
Arguments
property
Property to get the value of
Supported signatures
prop(String): Unknown
prop(Reference): Unknown
variable
Returns the value of the given variable in the exploration.
Arguments
name
Variable name to get the value of
variable(String): Unknown
coalesce
Returns first non-null value in arguments.
Arguments
value
Value to return if all preceding values are null
Supported signatures
coalesce(String, [String, ...]): String
coalesce(Enum, [Enum, ...]): Enum
coalesce(Number, [Number, ...]): Number
coalesce(Float, [Float, ...]): Float
coalesce(Integer, [Integer, ...]): Integer
coalesce(Number | Float | Integer, [Number | Float | Integer, ...]): Number
coalesce(Boolean, [Boolean, ...]): Boolean
coalesce(Date, [Date, ...]): Date
coalesce(Interval, [Interval, ...]): Interval
coalesce(Enum, [Enum, ...]): Enum
coalesce(String | Enum, [String | Enum, ...]): String
format
Formats the value to the given format in the final output.
Arguments
field
Field to formatformat
Format to display in. For all numeric fields, can be one of "eur", "usd" or "gbp". Floating-point number fields also accept "percentage". For date fields, can be one of "iso", "date" or "time".
Supported signatures
format(String, String): String
format(Number, String): Number
format(Float, String): Float
format(Integer, String): Integer
format(Boolean, String): Boolean
format(Date, String): Date
format(Interval, String): Interval
format(Enum, String): Enum