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

Example

Column A: prop("Plan") == 'pro' && prop("Payment problems")

Account	Status	Plan	Payment problems	Column A
Acct 1	active	pro	no	no
Acct 2	active	basic	no	no
Acct 3	suspended	pro	yes	yes
Acct 4	active	basic	yes	no

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

Example

Needs attention: prop("Status") == "idle" || prop("Urgent")

Account	Status	Urgent	Needs attention
Acct 1	active	no	no
Acct 2	idle	no	yes
Acct 3	active	yes	yes
Acct 4	closed	no	no

not

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

Arguments

• value Expression that represents a logical value

not(Boolean): Boolean

Example

Needs onboarding: not(prop("Is onboarded"))

Account	Onboarded	Needs onboarding
Acct 1	yes	no
Acct 2	no	yes
Acct 3	no	yes
Acct 4	yes	no

eq

Equality check. Returns true if two given values are equal

Arguments

• value1 First value
• value2 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

Example

Is active: prop("Status") == "active"

Account	Status	Is active
Acct 1	active	yes
Acct 2	inactive	no
Acct 3	active	yes

neq

Non-equality check. Returns false if two given values are equal, true otherwise.

Arguments

• value1 First value
• value2 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

Example

Not pending: prop("Status") != "pending"

Order	Status	Not pending
Order 1	completed	yes
Order 2	pending	no
Order 3	shipped	yes

gt

Returns true if the first value is greater than the second, false otherwise.

Arguments

• value1 Value to test being greater than value2
• value2 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

Example

High value: prop("Amount") > 1000

Order	Amount	High value
Order 1	1500.0	yes
Order 2	850.0	no
Order 3	2100.0	yes

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 value2
• value2 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

Example

Eligible: prop("Score") >= 75

Student	Score	Eligible
Student 1	85	yes
Student 2	75	yes
Student 3	68	no

lt

Returns true if the first value is less than the second, false otherwise.

Arguments

• value1 Value to test being less than value2
• value2 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

Example

Low stock: prop("Stock") < 10

Product	Stock	Low stock
Product 1	5	yes
Product 2	15	no
Product 3	8	yes

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 value2
• value2 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

Example

On budget: prop("Spent") <= prop("Budget")

Project	Spent	Budget	On budget
Project 1	8500	10000	yes
Project 2	12000	10000	no
Project 3	10000	10000	yes

in

Returns true if the value is one of the following values, false otherwise.

Arguments

• value Value to check
• values Values to check from

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

Example

Is priority: in(prop("Plan"), "pro", "enterprise")

Account	Plan	Is priority
Acct 1	free	no
Acct 2	pro	yes
Acct 3	basic	no
Acct 4	enterprise	yes

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

Example

Priority: case(prop("Plan") == "enterprise", "High", prop("Plan") == "pro", "Medium", "Low")

Account	Plan	Priority
Acct 1	enterprise	High
Acct 2	pro	Medium
Acct 3	basic	Low
Acct 4	free	Low

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

Example

Total: prop("Price") + prop("Tax") + prop("Shipping")

Order	Price	Tax	Shipping	Total
Order 1	99.00	8.9	5.00	112.9
Order 2	45.50	4.1	3.50	53.1
Order 3	78.25	7.0	4.75	90.0

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

Example

Profit: prop("Revenue") - prop("Costs")

Product	Revenue	Costs	Profit
Product 1	1500.00	950.0	550.0
Product 2	2300.50	1800	500.5
Product 3	875.25	600.5	274.75

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

Example

Total: prop("Quantity") * prop("Unit price")

Item	Quantity	Unit price	Total
Item 1	5	19.99	99.95
Item 2	12	7.50	90.00
Item 3	3	45.00	135.00

div

Returns the division of given values.

Arguments

• dividend The value to be divided
• divisor 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

Example

Average: prop("Total") / prop("Count")

Product	Total	Count	Average
Product 1	450.0	5	90.0
Product 2	780.0	12	65.0
Product 3	225.0	3	75.0

pow

Converts value to the power of the given exponent.

Arguments

• value Value to raise to the power of the exponent
• exponent (Optional) Exponent to raise the value to

Supported signatures

• pow(Number, Number): Number
• pow(Integer, Number): Integer
• pow(Float, Number): Float

Example

Squared: pow(prop("Value"), 2)

Item	Value	Squared
Item 1	3	9
Item 2	5	25
Item 3	8	64

sqrt

Returns the square root of the given value.

Arguments

• value Value to get the square root of

Supported signatures

• sqrt(Number): Number
• sqrt(Integer): Number
• sqrt(Float): Float

Example

Root: sqrt(prop("Area"))

Square	Area	Root
Square 1	9	3
Square 2	16	4
Square 3	25	5

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

Example

Highest: max(prop("Q1"), prop("Q2"), prop("Q3"))

Product	Q1	Q2	Q3	Highest
Product 1	120	145	138	145
Product 2	95	102	88	102
Product 3	225	198	210	225

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

Example

Lowest: min(prop("Q1"), prop("Q2"), prop("Q3"))

Product	Q1	Q2	Q3	Lowest
Product 1	120	145	138	120
Product 2	95	88	102	88
Product 3	210	198	225	198

ceil

Returns the smallest integer greater than or equal to the given value.

Arguments

• value Value to round up

ceil(Number | Float | Integer): Number

Example

Rounded up: ceil(prop("Value"))

Item	Value	Rounded up
Item 1	3.14	4
Item 2	7.89	8
Item 3	5.01	6

floor

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

Arguments

• value Value to round down

floor(Number | Float | Integer): Number

Example

Rounded down: floor(prop("Value"))

Item	Value	Rounded down
Item 1	3.14	3
Item 2	7.89	7
Item 3	5.99	5

round

Returns the value rounded to the nearest integer.

Arguments

• value Value to round to the nearest integer
• precision An integer specifying the number of significant digits

Supported signatures

• round(Number | Float | Integer, Number): Number
• round(Number | Float | Integer): Number

Example

Rounded: round(prop("Value"), 1)

Item	Value	Rounded
Item 1	3.14	3.1
Item 2	7.89	7.9
Item 3	5.55	5.6

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 string
• lookup_string String to search for within the search string

Supported signatures

• contains(String, String): Boolean
• contains(Enum, String): Boolean

Example

Has keyword: contains(prop("Description"), "urgent")

Task	Description	Has keyword
Task 1	Fix urgent bug	yes
Task 2	Review code	no
Task 3	URGENT: Deploy hotfix	yes
Task 4	Update documentation	no

Case-insensitive search for substring within text.

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 split
• delimiter Delimiter to split the string by
• position Position of the part to return

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

Example

Domain: split_part(prop("Email"), "@", 2)

User	Email	Domain
User 1	alice@example.com	example.com
User 2	bob@company.org	company.org
User 3	charlie@domain.net	domain.net

Extracts the domain from email addresses using '@' as delimiter.

substring

Returns a substring of the given string from the start position for the specified length.

Arguments

• string String to extract the substring from
• start Start position (1-based), negative values count from the end
• length Length of the substring, cannot be negative

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

Example

Code: substring(prop("Product ID"), 1, 3)

Product	Product ID	Code
Product 1	PRD-12345	PRD
Product 2	ACC-67890	ACC
Product 3	SRV-24680	SRV

Extracts the first 3 characters starting from position 1.

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

Example

Full name: concat(prop("First name"), " ", prop("Last name"))

User	First name	Last name	Full name
User 1	Alice	Smith	Alice Smith
User 2	Bob	Johnson	Bob Johnson
User 3	Charlie	Williams	Charlie Williams

Combines first and last names with a space separator.

len

Returns the length of the string from the given value.

Arguments

• string String to get the length of

len(String | Enum): Integer

Example

Length: len(prop("Title"))

Article	Title	Length
Article 1	Getting Started with TypeScript	31
Article 2	Advanced React Patterns	23
Article 3	API Design	10

Counts the number of characters in each string.

Date functions

date

Returns the given value as a Date.

Arguments

• value Value to convert to a date

date(String | Date): Date

Example

Parsed: date(prop("Created"))

Event	Created	Parsed
Event 1	2024-03-15	2024-03-15
Event 2	2024-06-20	2024-06-20
Event 3	2024-09-10	2024-09-10

now

Returns the current date and time.

Arguments

now(): Date

Example

Current time: now()

Returns the current date and time when the query executes, for example: 2024-03-15 14:30:45

year

Extracts the year from the given date value.

Arguments

• date Date value to extract the year from

year(Date): Integer

Example

Year: year(prop("Date"))

Event	Date	Year
Event 1	2023-03-15	2023
Event 2	2024-06-20	2024
Event 3	2024-09-10	2024

quarter

Extracts the quarter from the given date value.

Arguments

• date Date value to extract the quarter from

quarter(Date): Integer

Example

Quarter: quarter(prop("Date"))

Event	Date	Quarter
Event 1	2024-02-15	1
Event 2	2024-06-20	2
Event 3	2024-11-10	4

month

Extracts the month from the given date value.

Arguments

• date Date value to extract the month from

month(Date): Integer

Example

Month: month(prop("Date"))

Event	Date	Month
Event 1	2024-02-15	2
Event 2	2024-06-20	6
Event 3	2024-11-10	11

week

Extracts the week number from the given date value.

Arguments

• date Date value to extract the week number from

week(Date): Integer

Example

Week: week(prop("Date"))

Event	Date	Week
Event 1	2024-01-08	2
Event 2	2024-06-20	25
Event 3	2024-12-30	53

day

Extracts the day from the given date value.

Arguments

• date Date value to extract the day from

day(Date): Integer

Example

Day: day(prop("Date"))

Event	Date	Day
Event 1	2024-03-05	5
Event 2	2024-06-20	20
Event 3	2024-09-15	15

hour

Extracts the hour from the given date value.

Arguments

• date Date value to extract the hour from

hour(Date): Integer

Example

Hour: hour(prop("Timestamp"))

Event	Timestamp	Hour
Event 1	2024-03-15 08:30:00	8
Event 2	2024-06-20 14:45:00	14
Event 3	2024-09-10 22:15:00	22

minute

Extracts the minute from the given date value.

Arguments

• date Date value to extract the minute from

minute(Date): Integer

Example

Minute: minute(prop("Timestamp"))

Event	Timestamp	Minute
Event 1	2024-03-15 08:15:00	15
Event 2	2024-06-20 14:45:00	45
Event 3	2024-09-10 22:30:00	30

second

Extracts the second from the given date value.

Arguments

• date Date value to extract the second from

second(Date): Integer

Example

Second: second(prop("Timestamp"))

Event	Timestamp	Second
Event 1	2024-03-15 08:30:12	12
Event 2	2024-06-20 14:45:38	38
Event 3	2024-09-10 22:15:55	55

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): Integer

Example

Day of week: day_of_week(prop("Date"))

Event	Date	Day of week
Event 1	2024-03-11	1
Event 2	2024-06-20	4
Event 3	2024-09-15	7

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', 'quarter', 'month', 'week', 'day', 'hour', 'minute', 'second', 'millisecond')

interval(Integer | Number, String): Interval

Example

Duration: interval(prop("Count"), prop("Unit"))

Task	Count	Unit	Duration
Task 1	5	days	5 days
Task 2	2	weeks	2 weeks
Task 3	3	months	3 months

extract

Extracts given date part from the given interval.

Arguments

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

extract(Interval, String | Enum): Number

Example

Days: extract(prop("Duration"), "days")Months: extract(prop("Duration"), "months")

Task	Duration	Days	Months
Task 1	5 days	5	0
Task 2	2 weeks	14	0
Task 3	3 months	90	3

Extracts the number of days or months from an interval value.

date_diff

Calculates the difference between two dates in the specified unit.

Arguments

• date1 First date value
• date2 Second date value
• unit Unit of the result (allowed values: 'year', 'quarter', 'month', 'week', 'day', 'hour', 'minute', 'second')

date_diff(Date, Date, String | Enum): Number

Example

Days: date_diff(prop("End date"), prop("Start date"), "days")

Project	Start date	End date	Days
Project 1	2024-03-01	2024-03-15	14
Project 2	2024-06-01	2024-06-20	19
Project 3	2024-09-01	2024-10-05	34

net_work_days

Returns the number of net work days (business days) between two dates.

Arguments

• start_date Start date
• end_date End date

net_work_days(Date, Date): Integer

Example

Work days: net_work_days(prop("Start date"), prop("End date"))

Project	Start date	End date	Work days
Project 1	2024-03-01	2024-03-15	10
Project 2	2024-06-03	2024-06-14	9
Project 3	2024-09-02	2024-09-20	14

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

Example

Year start: start_of_year(prop("Date"))

Event	Date	Year start
Event 1	2024-03-15	2024-01-01
Event 2	2024-06-20	2024-01-01
Event 3	2024-09-05	2024-01-01

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

Example

Year end: end_of_year(prop("Date"))

Event	Date	Year end
Event 1	2024-03-15	2024-12-31
Event 2	2024-06-20	2024-12-31
Event 3	2024-09-05	2024-12-31

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

Example

Quarter start: start_of_quarter(prop("Date"))

Event	Date	Quarter start
Event 1	2024-02-15	2024-01-01
Event 2	2024-06-20	2024-04-01
Event 3	2024-11-10	2024-10-01

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

Example

Quarter end: end_of_quarter(prop("Date"))

Event	Date	Quarter end
Event 1	2024-02-15	2024-03-31
Event 2	2024-06-20	2024-06-30
Event 3	2024-11-10	2024-12-31

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

Example

Month start: start_of_month(prop("Date"))

Event	Date	Month start
Event 1	2024-03-15	2024-03-01
Event 2	2024-06-20	2024-06-01
Event 3	2024-09-05	2024-09-01

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

Example

Month end: end_of_month(prop("Date"))

Event	Date	Month end
Event 1	2024-03-15	2024-03-31
Event 2	2024-06-20	2024-06-30
Event 3	2024-09-05	2024-09-30

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

Example

Week start: start_of_week(prop("Date"))

Event	Date	Week start
Event 1	2024-03-13	2024-03-11
Event 2	2024-06-20	2024-06-17
Event 3	2024-09-05	2024-09-02

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

Example

Week end: end_of_week(prop("Date"))

Event	Date	Week end
Event 1	2024-03-13	2024-03-17
Event 2	2024-06-20	2024-06-23
Event 3	2024-09-05	2024-09-08

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

Example

Day start: start_of_day(prop("Timestamp"))

Event	Timestamp	Day start
Event 1	2024-03-15 14:30:45	2024-03-15 00:00:00
Event 2	2024-06-20 09:15:30	2024-06-20 00:00:00
Event 3	2024-09-10 22:45:12	2024-09-10 00:00:00

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

Example

Day end: end_of_day(prop("Timestamp"))

Event	Timestamp	Day end
Event 1	2024-03-15 14:30:45	2024-03-15 23:59:59
Event 2	2024-06-20 09:15:30	2024-06-20 23:59:59
Event 3	2024-09-10 22:45:12	2024-09-10 23:59:59

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

Example

Hour start: start_of_hour(prop("Timestamp"))

Event	Timestamp	Hour start
Event 1	2024-03-15 14:30:45	2024-03-15 14:00:00
Event 2	2024-06-20 09:15:30	2024-06-20 09:00:00
Event 3	2024-09-10 22:45:12	2024-09-10 22:00:00

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

Example

Hour end: end_of_hour(prop("Timestamp"))

Event	Timestamp	Hour end
Event 1	2024-03-15 14:30:45	2024-03-15 14:59:59
Event 2	2024-06-20 09:15:30	2024-06-20 09:59:59
Event 3	2024-09-10 22:45:12	2024-09-10 22:59:59

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

Example

Minute start: start_of_minute(prop("Timestamp"))

Event	Timestamp	Minute start
Event 1	2024-03-15 14:30:45	2024-03-15 14:30:00
Event 2	2024-06-20 09:15:30	2024-06-20 09:15:00
Event 3	2024-09-10 22:45:12	2024-09-10 22:45:00

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

Example

Minute end: end_of_minute(prop("Timestamp"))

Event	Timestamp	Minute end
Event 1	2024-03-15 14:30:45	2024-03-15 14:30:59
Event 2	2024-06-20 09:15:30	2024-06-20 09:15:59
Event 3	2024-09-10 22:45:12	2024-09-10 22:45:59

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

Example

Name: json_get_text(prop("Data"), "name")

User	Data	Name
User 1	{"name": "Alice", "age": 30}	Alice
User 2	{"name": "Bob", "age": 25}	Bob
User 3	{"name": "Charlie", "age": 35}	Charlie

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

Example

Price: json_get_number(prop("Data"), "price")

Product	Data	Price
Product 1	{"price": 19.99, "stock": 10}	19.99
Product 2	{"price": 45.50, "stock": 5}	45.50
Product 3	{"price": 12.75, "stock": 20}	12.75

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

Example

Stock: json_get_integer(prop("Data"), "stock")

Product	Data	Stock
Product 1	{"price": 19.99, "stock": 10}	10
Product 2	{"price": 45.50, "stock": 5}	5
Product 3	{"price": 12.75, "stock": 20}	20

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

Example

Rating: json_get_float(prop("Data"), "rating")

Product	Data	Rating
Product 1	{"rating": 4.5, "reviews": 120}	4.5
Product 2	{"rating": 3.8, "reviews": 85}	3.8
Product 3	{"rating": 4.9, "reviews": 200}	4.9

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

Example

Created: json_get_timestamptz(prop("Data"), "created")

Order	Data	Created
Order 1	{"created": "2024-03-15", "status": "ok"}	2024-03-15
Order 2	{"created": "2024-06-20", "status": "ok"}	2024-06-20
Order 3	{"created": "2024-09-10", "status": "ok"}	2024-09-10

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

Example

Active: json_get_boolean(prop("Data"), "active")

Account	Data	Active
Account 1	{"active": true, "verified": yes}	yes
Account 2	{"active": false, "verified": no}	no
Account 3	{"active": true, "verified": yes}	yes

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

Example

As integer: int(prop("Price"))

Product	Price	As integer
Item A	9.99	9
Item B	15.50	15
Item C	7.25	7

Converts decimal values to integers by truncating.

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

Example

As float: float(prop("Count"))

Product	Count	As float
Item A	5	5.00
Item B	12	12.00
Item C	8	8.00

Converts integers to floating point numbers.

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

Example

As string: str(prop("Value"))

Product	Value	As string
Item A	42	"42"
Item B	3.14	"3.14"
Item C	true	"true"

Converts values to their string representation.

bool

Returns the given value as a boolean.

Arguments

• value Value to convert to a boolean

bool(Boolean | Number | Integer): Boolean

Example

As boolean: bool(prop("Active"))

Product	Active	As boolean
Item A	1	yes
Item B	0	no
Item C	5	yes

Converts numbers to booleans. Zero becomes false, non-zero becomes true.

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 | 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
• sum(Number | Float | Integer, String | Enum | Date | Number | Integer | Float | Boolean, String): Number

Example

Regional total: sum(prop("Revenue"), prop("Region"))

Region	Product	Revenue	Regional total
North	Laptop	3000.00	4500.00
North	Mouse	500.00	4500.00
North	Monitor	1000.00	4500.00
South	Phone	2000.00	3500.00
South	Tablet	1500.00	3500.00
East	Desk	1200.00	2000.00
East	Chair	800.00	2000.00

Shows total revenue per region. Each region's rows display the same regional total.

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(): 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

Example

Orders per region: count(prop("Region"))

Region	Product	Orders per region
North	Laptop	3
North	Mouse	3
North	Monitor	3
South	Phone	2
South	Tablet	2
East	Desk	2
East	Chair	2

Shows number of orders per region. Each region's rows display the same count.

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 | 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
• avg(Number | Float | Integer, String | Enum | Date | Number | Integer | Float | Boolean, String): Number

Example

Regional average: avg(prop("Revenue"), prop("Region"))

Region	Product	Revenue	Regional average
North	Laptop	3000.00	1500.00
North	Mouse	500.00	1500.00
North	Monitor	1000.00	1500.00
South	Phone	2000.00	1750.00
South	Tablet	1500.00	1750.00
East	Desk	1200.00	1000.00
East	Chair	800.00	1000.00

Shows average revenue per region. Each region's rows display the same average.

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 aggregate
• sort_field (Optional) Field to sort by
• sort_direction (Optional) Sort direction ("asc"/"desc")
• range_start (Optional) Start index of range to find the minimum value over, relative to the current row
• range_end (Optional) End index of range to find the minimum value over, relative to the current row
• group (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_min(String | Enum, String | Enum | Date | Number | Integer | Float | Boolean, String): String
• range_min(Date, String | Enum | Date | Number | Integer | Float | Boolean, String): Date
• range_min(Number | Float | Integer, String | Enum | Date | Number | Integer | Float | Boolean, String): Number

Example

Regional minimum: range_min(prop("Revenue"), prop("Region"))

Region	Product	Revenue	Regional minimum
North	Laptop	3000.00	500.00
North	Mouse	500.00	500.00
North	Monitor	1000.00	500.00
South	Phone	2000.00	1500.00
South	Tablet	1500.00	1500.00
East	Desk	1200.00	800.00
East	Chair	800.00	800.00

Shows minimum revenue per region. Each region's rows display the same minimum.

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 aggregate
• sort_field (Optional) Field to sort by
• sort_direction (Optional) Sort direction ("asc"/"desc")
• range_start (Optional) Start index of range to find the minimum value over, relative to the current row
• range_end (Optional) End index of range to find the minimum value over, relative to the current row
• group (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
• range_max(String | Enum, String | Enum | Date | Number | Integer | Float | Boolean, String): String
• range_max(Date, String | Enum | Date | Number | Integer | Float | Boolean, String): Date
• range_max(Number | Float | Integer, String | Enum | Date | Number | Integer | Float | Boolean, String): Number

Example

Regional maximum: range_max(prop("Revenue"), prop("Region"))

Region	Product	Revenue	Regional maximum
North	Laptop	3000.00	3000.00
North	Mouse	500.00	3000.00
North	Monitor	1000.00	3000.00
South	Phone	2000.00	2000.00
South	Tablet	1500.00	2000.00
East	Desk	1200.00	1200.00
East	Chair	800.00	1200.00

Shows maximum revenue per region. Each region's rows display the same maximum.

first_value

Returns the first value 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 find the minimum value over, relative to the current row
• range_end (Optional) End index of range to find the minimum value over, relative to the current row
• group (Optional) Field to group by

Supported signatures

• first_value(String | Enum): String
• first_value(Number | Float | Integer): Number
• first_value(Date): Date
• first_value(Interval): Interval
• first_value(Boolean): Boolean
• first_value(Object): Object
• first_value(String | Enum, String | Enum | Date | Number | Integer | Float | Boolean, String, Number, Number): String
• first_value(Number | Float | Integer, String | Enum | Date | Number | Integer | Float | Boolean, String, Number, Number): Number
• first_value(Date, String | Enum | Date | Number | Integer | Float | Boolean, String, Number, Number): Date
• first_value(Interval, String | Enum | Date | Number | Integer | Float | Boolean, String, Number, Number): Interval
• first_value(Boolean, String | Enum | Date | Number | Integer | Float | Boolean, String, Number, Number): Boolean
• first_value(Object, String | Enum | Date | Number | Integer | Float | Boolean, String, Number, Number): Object
• first_value(String | Enum, String | Enum | Date | Number | Integer | Float | Boolean, String, Number, Number, String | Enum | Date | Number | Integer | Float | Boolean): String
• first_value(Number | Float | Integer, String | Enum | Date | Number | Integer | Float | Boolean, String, Number, Number, String | Enum | Date | Number | Integer | Float | Boolean): Number
• first_value(Date, String | Enum | Date | Number | Integer | Float | Boolean, String, Number, Number, String | Enum | Date | Number | Integer | Float | Boolean): Date
• first_value(Interval, String | Enum | Date | Number | Integer | Float | Boolean, String, Number, Number, String | Enum | Date | Number | Integer | Float | Boolean): Interval
• first_value(Boolean, String | Enum | Date | Number | Integer | Float | Boolean, String, Number, Number, String | Enum | Date | Number | Integer | Float | Boolean): Boolean
• first_value(Object, String | Enum | Date | Number | Integer | Float | Boolean, String, Number, Number, String | Enum | Date | Number | Integer | Float | Boolean): Object
• first_value(String | Enum, String | Enum | Date | Number | Integer | Float | Boolean): String
• first_value(Number | Float | Integer, String | Enum | Date | Number | Integer | Float | Boolean): Number
• first_value(Date, String | Enum | Date | Number | Integer | Float | Boolean): Date
• first_value(Interval, String | Enum | Date | Number | Integer | Float | Boolean): Interval
• first_value(Boolean, String | Enum | Date | Number | Integer | Float | Boolean): Boolean
• first_value(Object, String | Enum | Date | Number | Integer | Float | Boolean): Object
• first_value(String | Enum, String | Enum | Date | Number | Integer | Float | Boolean, String): String
• first_value(Number | Float | Integer, String | Enum | Date | Number | Integer | Float | Boolean, String): Number
• first_value(Date, String | Enum | Date | Number | Integer | Float | Boolean, String): Date
• first_value(Interval, String | Enum | Date | Number | Integer | Float | Boolean, String): Interval
• first_value(Boolean, String | Enum | Date | Number | Integer | Float | Boolean, String): Boolean
• first_value(Object, String | Enum | Date | Number | Integer | Float | Boolean, String): Object

Example

First order: first_value(prop("Product"), prop("Date"), "asc", null, null, prop("Region"))

Region	Date	Product	First order
North	2024-01-01	Laptop	Laptop
North	2024-01-03	Mouse	Laptop
North	2024-01-05	Monitor	Laptop
South	2024-01-02	Phone	Phone
South	2024-01-04	Tablet	Phone
South	2024-01-06	Charger	Phone
East	2024-01-02	Desk	Desk
East	2024-01-07	Chair	Desk

Shows the first product ordered in each region when sorted by date.

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

Example

Share: percentage(prop("Revenue"), prop("Region"))

Account	Region	Revenue	Share
Acct 1	North	3000.00	0.75
Acct 2	North	1000.00	0.25
Acct 3	South	1500.00	0.60
Acct 4	South	1000.00	0.40

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

Example

Previous: offset(prop("Revenue"), -1, prop("Date"), "asc")

Date	Revenue	Previous
2024-01-01	1250.50
2024-01-02	3420.00	1250.50
2024-01-03	890.25	3420.00
2024-01-04	2100.00	890.25

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

Example

Rank: row_number(prop("Revenue"), "desc", prop("Region"))

Account	Region	Revenue	Rank
Acct 1	North	3420.00	1
Acct 2	North	1250.50	2
Acct 3	South	2100.00	1
Acct 4	South	890.25	2

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 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".
• precision Optional. Displays the formatted value up to the given precision for Date fields and as the given precision for Integer fields. Can be one of "year", "quarter", "month", "week", "day", "hour", "day_of_week"

Supported signatures

• format(String, String): String
• format(Number, String): Number
• format(Float, String): Float
• format(Integer, String): Integer
• format(Integer, String, String): Integer
• format(Boolean, String): Boolean
• format(Date, String): Date
• format(Date, String, String): Date
• format(Interval, String): Interval
• format(Enum, String): Enum

Example

Formatted: format(prop("Revenue"), "eur")

Account	Revenue	Formatted
Acct 1	1250.50	€1,250.50
Acct 2	890.25	€890.25
Acct 3	3420.00	€3,420.00