Supersimple

Appearance

Formula reference ​

Logic functions ​

and ​

Returns true if all the given values are true, false otherwise.

and(Boolean, [Boolean, ...]): Boolean

or ​

Returns true if at least one of the given values is true, false otherwise.

or(Boolean, [Boolean, ...]): Boolean

not ​

Returns true if the given value is false, false otherwise.

not(Boolean): Boolean

eq ​

Equality check. Returns true if two given values are equal, false otherwise.

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.

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.

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.

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.

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.

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 match
  • value Value to return if the preceding condition is true
  • default (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.

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.

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.

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.

Supported signatures

  • div(Number, [Number, ...]): Number
  • div(Float, [Float, ...]): Float
  • div(Integer, [Integer, ...]): Integer
  • div(Number | Float | Integer, [Number | Float | Integer, ...]): Number
  • div(Interval, Number): Interval

max ​

Returns the maximum value from the given values.

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.

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.

ceil(Number | Float | Integer): Number

floor ​

Returns the largest integer less than or equal to the given value.

floor(Number | Float | Integer): Number

round ​

Returns the value rounded to the nearest integer.

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.

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.

split_part(String | Enum, String, Number): String

concat ​

Concatenates given values into a single string.

concat(String | Number | Float | Integer | Enum, [String | Number | Float | Integer | Enum, ...]): String

Date functions ​

date ​

Returns the given value as a Date.

date(String | Date): Date

now ​

Returns the current date and time.

now(): Date

year ​

Extracts the year from the given date value.

year(Date): Number

month ​

Extracts the month from the given date value.

month(Date): Number

week ​

Extracts the week number from the given date value.

week(Date): Number

day ​

Extracts the day from the given date value.

day(Date): Number

hour ​

Extracts the hour from the given date value.

hour(Date): Number

minute ​

Extracts the minute from the given date value.

minute(Date): Number

second ​

Extracts the second from the given date value.

second(Date): Number

day_of_week ​

Extracts the day of the week from the given date value.

day_of_week(Date): Number

Date utility functions ​

interval ​

Creates an interval value of the given duration.

Arguments

  • count How many periods in the interval
  • period Length of one period (allowed values: 'year', 'month', 'week', 'day', 'hour', 'minute', 'second', 'millisecond')

interval(Number, String): Interval

extract ​

Extracts given date part from the given interval.

Arguments

  • interval Interval value
  • field Date part to extract (allowed values: 'year', 'month', 'week', 'day', 'hour', 'minute', 'second', 'millisecond')

extract(Interval, String): Number

date_diff ​

Returns the difference between two dates in the given units.

Arguments

  • start Start date
  • end End date
  • unit Unit of the result (allowed values: 'year', 'month', 'week', 'day', 'hour', 'minute', 'second', 'millisecond')

date_diff(Date, Date, String): Number

start_of_year ​

Returns the given date value truncated to the start of the year.

start_of_year(Date): Date

end_of_year ​

Return the given date value truncated to the end of the year / start of next year.

end_of_year(Date): Date

start_of_quarter ​

Returns the given date value truncated to the start of the quarter.

start_of_quarter(Date): Date

end_of_quarter ​

Return the given date value truncated to the end of the quarter / start of next quarter.

end_of_quarter(Date): Date

start_of_month ​

Returns the given date value truncated to the start of the month.

start_of_month(Date): Date

end_of_month ​

Returns the given date value truncated to the end of the month / start of next month.

end_of_month(Date): Date

start_of_week ​

Returns the given date value truncated to the start of the week.

start_of_week(Date): Date

end_of_week ​

Returns the given date value truncated to the end of the week / start of next week.

end_of_week(Date): Date

start_of_day ​

Returns the given date value truncated to the start of the day.

start_of_day(Date): Date

end_of_day ​

Returns the given date value truncated to the end of the day / start of next day.

end_of_day(Date): Date

start_of_hour ​

Returns the given date value truncated to the start of the hour.

start_of_hour(Date): Date

end_of_hour ​

Returns the given date value truncated to the end of the hour / start of next hour.

end_of_hour(Date): Date

start_of_minute ​

Returns the given date value truncated to the start of the minute.

start_of_minute(Date): Date

end_of_minute ​

Returns the given date value truncated to the end of the minute / start of next minute.

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 object
  • key 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 object
  • key 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 object
  • key 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 object
  • key 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 object
  • key 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 object
  • key Key to get the value of

json_get_boolean(Object, String): Boolean

Type conversions ​

int ​

Returns the given value as an integer.

int(Boolean | String | Enum | Number | Float | Integer): Integer

float ​

Returns the given value as a floating point number.

float(String | Enum | Number | Float | Integer): Float

str ​

Returns the given value as a string.

str(Boolean | String | Enum | Number | Float | Integer | Date | Object | Interval): String

bool ​

Returns the given value as 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 aggregate
  • sort_field (Optional) Field to sort by
  • sort_direction (Optional) Sort direction ("asc"/"desc")
  • range_start (Optional) Start index of range to sum over, relative to the current row
  • range_end (Optional) End index of range to sum over, relative to the current row
  • group (Optional) Field to group by

Supported signatures

  • sum(Number | Integer | Float): Number
  • sum(Number | Integer | Float, String | Enum | Date | Number | Integer | Float | Boolean, String, Number, Number): Number
  • sum(Number | Integer | Float, String | Enum | Date | Number | Integer | Float | Boolean, String, Number, Number, String | Enum | Date | Number | Integer | Float | Boolean): Number
  • sum(Number | Integer | Float, 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 by
  • sort_direction (Optional) Sort direction ("asc"/"desc")
  • range_start (Optional) Start index of range to count from, relative to the current row
  • range_end (Optional) End index of range to count to, relative to the current row
  • group (Optional) Field to group by

Supported signatures

  • count(): Number
  • count(String | Enum | Date | Number | Integer | Float | Boolean): Number
  • count(String | Enum | Date | Number | Integer | Float | Boolean, String, Number, Number): Number
  • count(String | Enum | Date | Number | Integer | Float | Boolean, String, Number, Number, String | Enum | Date | Number | Integer | Float | Boolean): Number

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 aggregate
  • sort_field (Optional) Field to sort by
  • sort_direction (Optional) Sort direction ("asc"/"desc")
  • range_start (Optional) Start index of range to average over, relative to the current row
  • range_end (Optional) End index of range to average over, relative to the current row
  • group (Optional) Field to group by

Supported signatures

  • avg(Number | Integer | Float): Number
  • avg(Number | Integer | Float, String | Enum | Date | Number | Integer | Float | Boolean, String, Number, Number): Number
  • avg(Number | Integer | Float, String | Enum | Date | Number | Integer | Float | Boolean, String, Number, Number, String | Enum | Date | Number | Integer | Float | Boolean): Number
  • avg(Number | Integer | Float, 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 of
  • group (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 offset
  • offset Offset index (<0 will result in previous values, e.g. -2)
  • sort_field Field to sort by
  • sort_direction Sort direction ("asc"/"desc")
  • group (Optional) Field to group by

Supported signatures

  • offset(String | Enum, Number, String | Enum | Date | Number | Integer | Float | Boolean, String?): String
  • 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 | Enum, Number, String | Enum | Date | Number | Integer | Float | Boolean, String, String | Enum | Date | Number | Integer | Float | Boolean): String
  • 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 by
  • sort_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.

Supported signatures

  • prop(String): Unknown
  • prop(Reference): Unknown

variable ​

Returns the value of the given variable in the exploration.

variable(String): Unknown

coalesce ​

Returns first non-null value in arguments.

Supported signatures

  • coalesce(String, [String, ...]): String
  • 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 format
  • format 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

prql ​

Returns the value of the given raw PRQL query. Use this as an escape hatch for advanced use cases.

prql(String): Unknown