AQL Cheatsheet - Functions
Aggregate Function
count()
Syntax
count([table], expression)
Description
Counts the total number of items in a group, excluding NULL values.
Tags
Function, Aggregate Function
Examples
orders
| count(orders.id)
orders
| count(orders.id)
| where(orders.status == 'completed')
orders
| group(orders.delivery_country)
| select(orders.delivery_country, total_orders: count(orders.id))
Inputs
- expression (Any: required): A field or an AQL expression to be evaluated in each row of the table to be aggregated.
- table (Table: optional): The table to aggregate. It's only optional when the table can be inferred from the expression.
Output
- Number: The count of non-NULL values.
Learn more
count_if()
Syntax
count_if([table], condition)
Description
Counts the total rows from one table that satisfy the given condition.
Tags
Function, Aggregate Function, Condition
Examples
orders
| count_if(orders.delivery_country == 'USA')
orders
| count_if(orders.status == 'shipped')
orders
| group(orders.delivery_country)
| select(orders.delivery_country, shipped_orders: count_if(orders.status == 'shipped'))
Inputs
- condition (Truefalse: required): A field or an AQL expression that evaluates to true or false.
- table (Table: optional): The table to aggregate. It's only optional when the table can be inferred from the expression.
Output
- Number: The count of rows that satisfy the condition.
Learn more
count_distinct()
Syntax
count_distinct([table], expression)
Description
Counts the total number of distinct items in a group, excluding NULL values.
Tags
Function, Aggregate Function
Examples
orders
| count_distinct(orders.customer_id)
customers
| count_distinct(customers.id)
| where(customers.country == 'USA')
products
| group(products.id)
| select(products.id, total_countries: count_distinct(orders.delivery_country))
Inputs
- expression (Any: required): A field or an AQL expression to be evaluated in each row of the table to be aggregated.
- table (Table: optional): The table to aggregate. It's only optional when the table can be inferred from the expression.
Output
- Number: The count of distinct non-NULL values.
Learn more
average()
Syntax
average([table], expression)
Description
Calculates the average of values in a group, excluding NULL values.
Tags
Function, Aggregate Function
Examples
orders
| average(orders.value)
orders
| average(orders.value)
| where(orders.status == 'completed')
orders
| group(orders.delivery_country)
| select(orders.delivery_country, avg_order_value: average(orders.value))
Inputs
- expression (Number: required): A field or an AQL expression to be evaluated in each row of the table to be aggregated.
- table (Table: optional): The table to aggregate. It's only optional when the table can be inferred from the expression.
Output
- Number: The average of the values.
Aliases
avg()
Learn more
min()
Syntax
min([table], expression)
Description
Returns the item in the group with the smallest value, excluding NULL values.
Tags
Function, Aggregate Function
Examples
orders
| min(orders.value)
orders
| min(orders.value)
| where(orders.status == 'completed')
orders
| group(orders.delivery_country)
| select(orders.delivery_country, min_order_value: min(orders.value))
Inputs
- expression (Any: required): A field or an AQL expression to be evaluated in each row of the table to be aggregated.
- table (Table: optional): The table to aggregate. It's only optional when the table can be inferred from the expression.
Output
- Any: The minimum value.
Learn more
max()
Syntax
max([table], expression)
Description
Returns the item in the group with the largest value, excluding NULL values.
Tags
Function, Aggregate Function
Examples
orders
| max(orders.value)
orders
| max(orders.value)
| where(orders.status == 'completed')
orders
| group(orders.delivery_country)
| select(orders.delivery_country, max_order_value: max(orders.value))
Inputs
- expression (Any: required): A field or an AQL expression to be evaluated in each row of the table to be aggregated.
- table (Table: optional): The table to aggregate. It's only optional when the table can be inferred from the expression.
Output
- Any: The maximum value.
Learn more
sum()
Syntax
sum([table], expression)
Description
Calculates the sum of values in the group, excluding NULL values.
Tags
Function, Aggregate Function
Examples
orders
| sum(orders.value)
orders
| sum(orders.value)
| where(orders.status == 'completed')
orders
| group(orders.delivery_country)
| select(orders.delivery_country, total_order_value: sum(orders.value))
Inputs
- expression (Number: required): A field or an AQL expression to be evaluated in each row of the table to be aggregated.
- table (Table: optional): The table to aggregate. It's only optional when the table can be inferred from the expression.
Output
- Number: The sum of the values.
Learn more
median()
Syntax
median([table], expression)
Description
Computes the median of the values in the group, excluding NULL values.
Tags
Function, Aggregate Function
Examples
median(orders.quantity)
orders
| median(orders.quantity)
Inputs
- expression (Number: required): A field or an AQL expression to be evaluated in each row of the table to be aggregated.
- table (Table: optional): The table to aggregate. It's only optional when the table can be inferred from the expression.
Output
- Number: The median of the values.
Learn more
stdev()
Syntax
stdev([table], expression)
Description
Computes the sample standard deviation of the values in the group, excluding NULL values.
Tags
Function, Aggregate Function
Examples
stdev(orders.value)
orders
| stdev(orders.value)
Inputs
- expression (Number: required): A field or an AQL expression to be evaluated in each row of the table to be aggregated.
- table (Table: optional): The table to aggregate. It's only optional when the table can be inferred from the expression.
Output
- Number: The sample standard deviation.
Learn more
stdevp()
Syntax
stdevp([table], expression)
Description
Computes the population standard deviation of the values in the group, excluding NULL values.
Tags
Function, Aggregate Function
Examples
stdevp(orders.value)
orders
| stdevp(orders.value)
Inputs
- expression (Number: required): A field or an AQL expression to be evaluated in each row of the table to be aggregated.
- table (Table: optional): The table to aggregate. It's only optional when the table can be inferred from the expression.
Output
- Number: The population standard deviation.
Learn more
var()
Syntax
var([table], expression)
Description
Returns the sample variance of the values in the group, excluding NULL values.
Tags
Function, Aggregate Function
Examples
var(orders.value)
orders
| var(orders.value)
Inputs
- expression (Number: required): A field or an AQL expression to be evaluated in each row of the table to be aggregated.
- table (Table: optional): The table to aggregate. It's only optional when the table can be inferred from the expression.
Output
- Number: The sample variance.
Learn more
varp()
Syntax
varp([table], expression)
Description
Returns the population variance of the values in the group, excluding NULL values.
Tags
Function, Aggregate Function
Examples
varp(orders.value)
orders
| varp(orders.value)
Inputs
- expression (Number: required): A field or an AQL expression to be evaluated in each row of the table to be aggregated.
- table (Table: optional): The table to aggregate. It's only optional when the table can be inferred from the expression.
Output
- Number: The population variance.
Learn more
string_agg()
Syntax
string_agg([table], expression, [sep: _sep], [distinct: _distinct], [order: _order])
Description
Returns a text that is the concatenation of all values of the expression.
Tags
Function, Aggregate Function
Examples
string_agg(products.name)
string_agg(products.name, sep: ', ')
string_agg(products.name, sep: ', ', distinct: true)
string_agg(products.name, order: 'asc')
string_agg(products.name, order: 'desc')
Inputs
- expression (Text: required): A field or an AQL expression to be evaluated in each row of the table to be aggregated.
- table (Table: optional): The table to aggregate. It's only optional when the table can be inferred from the expression.
- _sep (Text: optional): Separator between values, default is
','
. - _distinct (Truefalse: optional): If true, only distinct values are concatenated, default is
false
. - _order (Text: optional): Specifies the ordering of values ('asc' or 'desc'), default is not specified.
Output
- Text: The concatenated text of values.
Learn more
corr()
Syntax
corr(table, field1, field2)
Description
Returns the Pearson correlation coefficient of two number fields in the table.
Tags
Function, Aggregate Function
Examples
corr(users, users.age, orders.value)
Inputs
- table (Table: required): The table containing the fields to correlate.
- field1 (Number: required): The first number field to correlate.
- field2 (Number: required): The second number field to correlate.
Output
- Number: The Pearson correlation coefficient between the two fields.
Learn more
max_by()
Syntax
max_by(table, value, by)
Description
Returns the value of value
from the row where by
is maximum.
Tags
Function, Aggregate Function
Examples
max_by(orders, orders.customer_name, orders.value)
Inputs
- table (Table: required): The table to find the maximum value in.
- value (Any: required): The field to return from the row with the maximum value.
- by (Any: required): The expression to use for determining the maximum value.
Output
- Any: The value of
value
from the row whereby
is maximum.
Learn more
min_by()
Syntax
min_by(table, value, by)
Description
Returns the value of value
from the row where by
is minimum.
Tags
Function, Aggregate Function
Examples
min_by(orders, orders.customer_name, orders.value)
Inputs
- table (Table: required): The table to find the minimum value in.
- value (Any: required): The field to return from the row with the minimum value.
- by (Any: required): The expression to use for determining the minimum value.
Output
- Any: The value of
value
from the row whereby
is minimum.
Learn more
percentile_cont()
Syntax
percentile_cont([table], expression, percentile)
Description
Returns the value at the given percentile of the sorted expression values, interpolating between adjacent values if needed.
Tags
Function, Aggregate Function
Examples
percentile_cont(ecommerce_users.age, 0.7)
ecommerce_users | percentile_cont(ecommerce_users.age, 0.7)
unique(ecommerce_users.id) | percentile_cont(total_value, 0.7)
Inputs
- expression (Any: required): A field or an AQL expression to be evaluated.
- table (Table: optional): The table to aggregate. It's only optional when the table can be inferred from the expression.
- percentile (Number: required): The percentile to compute. Must be a value between 0 and 1.
Output
- Any: The percentile value.
Learn more
percentile_disc()
Syntax
percentile_disc([table], expression, percentile)
Description
Returns the value at the given percentile of the sorted expression values. If the percentile falls between two values, a discrete value will be returned (the logic to select the value is database dependent).
Tags
Function, Aggregate Function
Examples
percentile_disc(ecommerce_users.age, 0.7)
ecommerce_users | percentile_disc(ecommerce_users.age, 0.7)
unique(ecommerce_users.id) | percentile_disc(count(ecommerce_orders.id), 0.7)
Inputs
- expression (Any: required): A field or an AQL expression to be evaluated.
- table (Table: optional): The table to aggregate. It's only optional when the table can be inferred from the expression.
- percentile (Number: required): The percentile to compute. Must be a value between 0 and 1.
Output
- Any: The percentile value.
Learn more
Condition Function
where()
Syntax
where(metric, condition1, [condition2, ...])
metric | where(condition1, [condition2, ...])
Description
Calculates a metric with specified conditions applied. The where
function allow applying conditions to a metric without needing to manually modifying its original definition and filters data at the source before any calculations occur. It works similar to Dashboard Filters.
Difference vs filter: where
pushes conditions down to the target model before calculations begin. In contrast, filter
evaluates conditions locally after retrieving the data. Due to this key difference, condition in where
can only target pre-defined dimension in modeling, and only support some form of conditions. For more information, please check the full documentation.
Tags
Function, Metric Function, Condition Function, Condition
Examples
total_order_value
| where(orders.status == 'completed')
total_order_value
| where(users.id in top(5, users.id, by: count(orders.id)))
sum(orders.value)
| where(orders.created_at matches @(last 30 days))
Inputs
- metric (Any: required): The original metric to which the condition will be applied.
- condition1 (Condition: required): The condition to restrict the metric calculation.
- [condition2, ...] (Condition: optional, repeatable): Additional conditions to apply.
Output
- Any: The original metric calculated with the specified conditions applied.
Learn more
Level Of Detail Function
of_all()
Syntax
of_all(metric, [model, dimension, ...], [keep_filters: false])
metric | of_all([model, dimension, ...], [keep_filters: false])
Description
Returns a metric evaluated without certain dimensions or grains. The of_all
function removes specified dimensions from the calculation context. It is commonly used for calculating percent of total and enables comparisons between metrics at different Levels of Detail (LoD). The function has aliases exclude_grains
and exclude
that provides identical functionality.
Tags
Function, Metric Function, Level of Detail Function
Examples
order_value / (
order_value
| of_all(order_items.country)
)
count(orders.id)
| of_all(orders.category)
order_value - (avg(unique(order_items.country)
| select(order_value))
| of_all(order_items.country))
Inputs
- metric (Any: required): The metric to exclude dimensions from.
- model (Model: optional, repeatable): Model whose dimensions should be excluded.
- dimension (Dimension: optional, repeatable): Specific dimensions to exclude.
- keep_filters (Truefalse: optional): Whether to keep filters applied on excluded dimensions. Default is false.
Output
- Any: The original metric calculation without the specified dimensions.
Aliases
exclude_grains(), exclude()
Learn more
keep_grains()
Syntax
keep_grains(metric, [model, dimension, ...], [keep_filters: false])
metric | keep_grains([model, dimension, ...], [keep_filters: false])
Description
Calculates a metric only against the specified dimensions or grains, ignoring all other dimensions. The keep_grains
function ensures that calculations maintain a consistent level of detail, prevents additional query fields from affecting the metric, and works only with dimensions already present in the context. Note that it will not add the grains to the Level of Detail context if they aren't present before calling keep_grains
.
Tags
Function, Metric Function, Level of Detail Function
Aliases
keep()
Examples
// This keeps the grain of the entire user model during the metric calculation
sum(order_items.order_value)
| keep_grains(users)
average_order_value
| keep_grains(users.id)
Inputs
- metric (Any: required): The input metric to modify.
- model (Model: optional, repeatable): Model whose dimensions should be kept.
- dimension (Dimension: optional, repeatable): Specific dimensions to keep.
- keep_filters (Truefalse: optional): Whether to keep filters applied on excluded dimensions. Default is false.
Output
- Any: The original metric calculation using only the specified dimensions.
Learn more
dimensionalize()
Syntax
dimensionalize(metric, [dimension, ...])
metric | dimensionalize([dimension, ...])
Description
Calculates a metric at a specific Level of Detail (LoD), regardless of the outer query context. The dimensionalize
function evaluates metrics at a fixed grain and turns the result into a dimension value. It is commonly used for cohort analysis and customer lifetime value calculations, and only works inside dimension definitions.
Difference vs keep_grains: Unlike keep_grains
which retains specified grains in metric calculation, dimensionalize
evaluates the calculation at specified grains and converts it into a dimension value. It only works inside dimension definitions.
Tags
Function, Metric Function, Level of Detail Function
Examples
sum(orders.amount)
| dimensionalize(users.id)
min(users.created_at | month())
| dimensionalize(users.id)
Inputs
- metric (Any: required): The metric to evaluate at a fixed grain.
- dimension (Dimension: optional, repeatable): The dimension(s) to use as the Level of Detail.
Output
- Any: The metric value at the specified granularity as a dimension value.
Learn more
Logical Function
case()
Syntax
case(when: condition, then: value, [when: condition_expr, then: value, ...], [else: value])
Description
Returns the value associated with the first condition that evaluates to true. Note that the return types of all then
and else
expressions must be consistent.
Tags
Function, Logical Function, Condition
Examples
case(
when: users.gender == 'm', then: 'male',
when: users.gender == 'f', then: 'female',
else: 'others'
)
Inputs
- when (Truefalse: required): The condition to evaluate.
- then (Any: required): The value to return if the condition is met.
- else (Any: optional): The value to return if no conditions are met.
Output
- Any: The value returned based on the first met condition, or the else value if provided and no conditions are met.
Learn more
and()
Syntax
and(condition, ...)
Description
Returns true only when all specified conditions are true.
Tags
Function, Logical Function, Condition
Examples
and(products.id >= 2, products.id <= 8)
Inputs
- condition (Truefalse: required, repeatable): The conditions to AND together.
Output
- Truefalse: Returns true if all input conditions are true, else false.
Learn more
or()
Syntax
or(condition, ...)
Description
Returns true when at least one of the specified conditions is true.
Tags
Function, Logical Function, Condition
Inputs
- condition (Truefalse: required, repeatable): The conditions to OR together.
Output
- Truefalse: Returns true if any input condition is true, else false.
Examples
or(products.id <= 2, products.id >= 8)
Learn more
not()
Syntax
not(condition)
Description
Logical NOT takes a single truefalse expression and returns true when the expression is false.
Tags
Function, Logical Function, Condition
Examples
not(is(products.id, null))
Inputs
- condition_expr (Truefalse: required): The condition to negate.
Output
- Truefalse: Returns true if the input condition is false, else false.
Learn more
Time-based Function
running_total()
Syntax
running_total(metric, [running_dimension, ...], [keep_filters: false])
metric | running_total([running_dimension, ...], [keep_filters: false])
Description
Calculates a running total of a metric along specified dimensions from the starting point to the current period. By default, the running total is calculated after filtering (keep_filters: true
). To calculate a running total of all data, ignoring any filters on the running dimensions, you can set keep_filters: false
.
Tags
Function, Metric Function, Time-based Function, Cumulative Calculation, Moving Calculation
Examples
running_total(orders.total_orders, orders.created_at)
running_total(orders.total_orders, orders.created_at, orders.status)
running_total(orders.total_orders, orders.created_at, keep_filters: false)
Inputs
- metric (Any: required): The metric to calculate the running total for.
- running_dimension (Dimension: optional, repeatable): The dimension(s) to run the calculation along. If not specified, the calculation will run along all date dimensions in the exploration.
- keep_filters (Truefalse: optional): Whether to keep the filters applied on the running dimensions. Default is true.
Output
- Any: A new metric that runs along the specified dimension(s).
Learn more
period_to_date()
Syntax
period_to_date(metric, date_part, date_dimension)
metric | period_to_date(date_part, date_dimension)
Description
Calculates a metric from the beginning of a specified time period (year, quarter, month, etc.) to the current date. This function is commonly used to calculate Year-to-Date (YTD), Quarter-to-Date (QTD), or Month-to-Date (MTD) metrics.
Note: "Current date" refers to the last date in the context of the current row, not the current month/period on your calendar.
Tags
Function, Metric Function, Time-based Function, Cumulative Calculation, Moving Calculation
Examples
count(orders.id)
| period_to_date('year', orders.created_at)
count(orders.id)
| period_to_date('month', orders.created_at)
Inputs
- metric (Any: required): The metric to apply the period_to_date function to.
- date_part (Text: required): The time period for which the metric should reset. Can be one of: 'year', 'quarter', 'month', 'week', 'day'.
- date_dimension (Dimension: required): The date dimension used to determine the reset period.
Output
- Any: The input metric calculation from the beginning of the specified time period to the current date.
Learn more
exact_period()
Syntax
exact_period(metric, time_dimension, time_range)
metric | exact_period(time_dimension, time_range)
Description
Calculates a metric within a custom time period. This function can be used to compare how a metric performs in a specific period compared to another period. It overrides any applied time filters.
Tags
Function, Metric Function, Time-based Function
Examples
exact_period(orders.total_orders, orders.created_at, @2022-07-01 - 2022-09-01)
orders.total_orders
| exact_period(orders.created_at, @2022-07-01 - 2022-09-01)
Inputs
- metric (Any: required): The metric to calculate within the custom period.
- time_dimension (Dimension: required): A pre-defined datetime/date dimension used for matching periods in the time range to the period in the dimension.
- time_range (Datetime: required): A datetime literal that specifies the exact time range, e.g.,
@2022-04-01
,@2022
,@(last 2 weeks)
.
Output
- Any: The input metric calculation for the specified custom period.
Learn more
relative_period()
Syntax
relative_period(metric, time_dimension, time_interval)
metric | relative_period(time_dimension, time_interval)
Description
Calculates a metric in the active time range shifted by a specified interval. The active time range can be the range specified in a filter (if no time dimension is active) or the time period in each row of a time dimension.
Tags
Function, Metric Function, Time-based Function
Examples
relative_period(orders.total_orders, orders.created_at, interval(-1 month))
orders.total_orders
| relative_period(orders.created_at, interval(-1 month))
Inputs
- metric (Any: required): The metric to calculate within the shifted time range.
- time_dimension (Dimension: required): A pre-defined datetime/date dimension used for shifting.
- time_interval (Interval: required): A relative interval for shifting from the active time condition, e.g.,
interval(-1 month)
.
Output
- Any: The input metric calculated in the active time range shifted by the specified interval.
Learn more
trailing_period()
Syntax
trailing_period(metric, date_dimension, period)
metric | trailing_period(date_dimension, period)
Description
Calculates a metric over a specific number of date periods up to the current period. This function is commonly used to calculate metrics like Trailing 3 Months, which determines the total orders in the last 3 months up until the current month.
Note: "Current period" refers to the month/period in the context of the current row, not the current month/period on your calendar.
Tags
Function, Metric Function, Time-based Function, Cumulative Calculation, Moving Calculation
Examples
trailing_period(count(orders.id), orders.created_at, interval(3 months))
count(orders.id)
| trailing_period(orders.created_at, interval(3 months))
Inputs
- metric (Any: required): The metric to apply the trailing_period function to.
- date_dimension (Dimension: required): The date dimension used to determine the periods.
- period (Interval: required): An interval literal that specifies the number of periods to calculate (includes the current period), e.g.,
interval(3 months)
,interval(1 year)
.
Output
- Any: The input metric calculated over the specified number of date periods up to the current period.
Learn more
Time Intelligence Function
epoch()
Syntax
epoch([datetime])
Description
Returns a Unix timestamp which is the number of seconds that have elapsed since '1970-01-01 00:00:00' UTC.
Tags
Function, Time Intelligence Function
Examples
epoch(orders.created_at)
epoch(@2022-01-03)
Inputs
- datetime (Datetime: optional): The date or datetime to convert to a Unix timestamp. If not provided, uses the current date time.
Output
- Number: Unix timestamp representing seconds since '1970-01-01 00:00:00' UTC.
day()
Syntax
day(datetime_dimension)
datetime_dimension | day()
Description
Truncates a datetime_dimension
value to the first day of the day (midnight).
Tags
Function, Time Intelligence Function
Examples
day(orders.created_at)
orders.created_at | day()
Inputs
- datetime_dimension (Datetime Dimension: required): The datetime to truncate to the day
Output
- Datetime Dimension: Truncated to the first moment of the day
week()
Syntax
week(datetime_dimension)
datetime_dimension | week()
Description
Truncates a datetime_dimension
value to the first day of the week. Weeks begin on the day set in the Week Start Day Setting.
Tags
Function, Time Intelligence Function
Examples
week(orders.created_at)
orders.created_at | week()
Inputs
- datetime_dimension (Datetime Dimension: required): The datetime to truncate to the week
Output
- Datetime Dimension: Truncated to the first moment of the week
month()
Syntax
month(datetime_dimension)
datetime_dimension | month()
Description
Truncates a datetime_dimension
value to the first day of the month.
Tags
Function, Time Intelligence Function
Examples
month(orders.created_at)
orders.created_at | month()
Inputs
- datetime_dimension (Datetime Dimension: required): The datetime to truncate to the month
Output
- Datetime Dimension: Truncated to the first moment of the month
quarter()
Syntax
quarter(datetime_dimension)
datetime_dimension | quarter()
Description
Truncates a datetime_dimension
value to the first day of the quarter.
Tags
Function, Time Intelligence Function
Examples
quarter(orders.created_at)
orders.created_at | quarter()
Inputs
- datetime_dimension (Datetime Dimension: required): The datetime to truncate to the quarter
Output
- Datetime Dimension: Truncated to the first moment of the quarter
year()
Syntax
year(datetime_dimension)
datetime_dimension | year()
Description
Truncates a datetime_dimension
value to the first day of the year.
Tags
Function, Time Intelligence Function
Examples
year(orders.created_at)
orders.created_at | year()
Inputs
- datetime_dimension (Datetime Dimension: required): The datetime to truncate to the year
Output
- Datetime Dimension: Truncated to the first moment of the year
hour()
Syntax
hour(datetime_dimension)
datetime_dimension | hour()
Description
Truncates a datetime_dimension
value to the first minute of the hour.
Tags
Function, Time Intelligence Function
Examples
hour(orders.created_at)
orders.created_at | hour()
Inputs
- datetime_dimension (Datetime Dimension: required): The datetime to truncate to the hour
Output
- Datetime Dimension: Truncated to the first moment of the hour
minute()
Syntax
minute(datetime_dimension)
datetime_dimension | minute()
Description
Truncates a datetime_dimension
value to the first second of the minute.
Tags
Function, Time Intelligence Function
Examples
minute(orders.created_at)
orders.created_at | minute()
Inputs
- datetime_dimension (Datetime Dimension: required): The datetime to truncate to the minute
Output
- Datetime Dimension: Truncated to the first moment of the minute
date_trunc()
Syntax
date_trunc(datetime_dimension, datetime_part)
Description
Truncates a datetime_dimension
value to the granularity of datetime_part
. The datetime
value is rounded to the beginning of datetime_part
.
Supported parts: 'day'
, 'week'
, 'month'
, 'quarter'
, 'year'
, 'hour'
, 'minute'
Tags
Function, Time Intelligence Function
Examples
date_trunc(orders.created_at, 'day')
date_trunc(orders.created_at, 'month')
date_trunc(orders.created_at, 'year')
Inputs
- datetime_dimension (Datetime Dimension: required): The datetime to truncate
- datetime_part (String: required): The granularity to truncate to
Output
- Datetime Dimension: Truncated to the beginning of the specified part
date_part()
Syntax
date_part(datetime_part, datetime)
Description
Extracts a specific numeric part from a date or datetime value. Returns the numeric representation of the specified part of the date.
Tags
Function, Time Intelligence Function
Examples
date_part('year', orders.created_at)
date_part('quarter', orders.created_at)
date_part('month', orders.created_at)
Inputs
- datetime_part (String: required): The specific part of the datetime to extract
- datetime (Datetime: required): The datetime to extract the part from
Output
- Number: Integer representing the specified part of the date
year_num()
Syntax
year_num(datetime)
datetime | year_num()
Description
Extracts the numeric year from a datetime value.
Tags
Function, Time Intelligence Function
Examples
year_num(orders.created_at)
orders.created_at | year_num()
Inputs
- datetime (Datetime Dimension: required): The datetime to extract the year from
Output
- Number: Numeric representation of the year
quarter_num()
Syntax
quarter_num(datetime)
datetime | quarter_num()
Description
Extracts the quarter number from a datetime value.
Tags
Function, Time Intelligence Function
Examples
quarter_num(orders.created_at)
orders.created_at | quarter_num()
Inputs
- datetime (Datetime Dimension: required): The datetime to extract the quarter from
Output
- Number: Quarter number (1-4)
month_num()
Syntax
month_num(datetime)
datetime | month_num()
Description
Extracts the month number from a datetime value.
Tags
Function, Time Intelligence Function
Examples
month_num(orders.created_at)
orders.created_at | month_num()
Inputs
- datetime (Datetime Dimension: required): The datetime to extract the month from
Output
- Number: Month number (1-12)
week_num()
Syntax
week_num(datetime)
datetime | week_num()
Description
Extracts the week number from a datetime value.
Tags
Function, Time Intelligence Function
Examples
week_num(orders.created_at)
orders.created_at | week_num()
Inputs
- datetime (Datetime Dimension: required): The datetime to extract the week number from
Output
- Number: Week number of the year (1-53)
dow_num()
Syntax
dow_num(datetime)
datetime | dow_num()
Description
Alias for dayofweek_num, extracts the day of week number from a datetime value.
Tags
Function, Time Intelligence Function
Examples
dow_num(orders.created_at)
orders.created_at | dow_num()
Inputs
- datetime (Datetime Dimension: required): The datetime to extract the day of week from
Output
- Number: Day of week number (0-6)
Aliases
dayofweek_num()
day_num()
Syntax
day_num(datetime)
datetime | day_num()
Description
Extracts the day of month number from a datetime value.
Tags
Function, Time Intelligence Function
Examples
day_num(orders.created_at)
orders.created_at | day_num()
Inputs
- datetime (Datetime Dimension: required): The datetime to extract the day from
Output
- Number: Day of month number (1-31)
hour_num()
Syntax
hour_num(datetime)
datetime | hour_num()
Description
Extracts the hour number from a datetime value.
Tags
Function, Time Intelligence Function
Examples
hour_num(orders.created_at)
orders.created_at | hour_num()
Inputs
- datetime (Datetime Dimension: required): The datetime to extract the hour from
Output
- Number: Hour number (0-23)
minute_num()
Syntax
minute_num(datetime)
datetime | minute_num()
Description
Extracts the minute number from a datetime value.
Tags
Function, Time Intelligence Function
Examples
minute_num(orders.created_at)
orders.created_at | minute_num()
Inputs
- datetime (Datetime Dimension: required): The datetime to extract the minute from
Output
- Number: Minute number (0-59)
second_num()
Syntax
second_num(datetime)
datetime | second_num()
Description
Extracts the second number from a datetime value.
Tags
Function, Time Intelligence Function
Examples
second_num(orders.created_at)
orders.created_at | second_num()
Inputs
- datetime (Datetime Dimension: required): The datetime to extract the second from
Output
- Number: Second number (0-59)
date_diff()
Syntax
date_diff(datetime_part, start, end)
Description
Calculates the difference between two dates in the specified datetime_part
.
Supported parts: 'day'
, 'week'
, 'month'
, 'quarter'
, 'year'
Tags
Function, Time Intelligence Function
Examples
date_diff('day', orders.created_at, @now)
date_diff('month', orders.created_at, @now)
Inputs
- datetime_part (String: required): The unit of measurement for the difference
- start (Datetime: required): The starting date
- end (Datetime: required): The ending date
Output
- Number: The difference between the two dates in the specified part
date_format()
Syntax
date_format(datetime, format)
Description
Formats a date according to the specified format string.
Tags
Function, Time Intelligence Function
Examples
date_format(orders.created_at, '%Y-%m-%d')
date_format(orders.created_at, '%Y-%m-%d %H:%M:%S')
date_format(orders.created_at, '%B %d, %Y')
date_format(orders.created_at, '%A, %B %d, %Y')
Inputs
- datetime (Datetime: required): The date to format
- format (Text: required): The format string
Output
- Text: The formatted date string
Format
Pattern | Description | Example |
---|---|---|
%Y | Four-digit year | 2018 |
%y | Two-digit year | 18 |
%q | Quarter of the year (1–4) | 3 |
%m | Two-digit month | 07 |
%B | Full month name | July |
%b | Abbreviated month name | Jul |
%A | Full day of week | Sunday |
%a | Abbreviated day of week | Sun |
%d | Two-digit day of month (01-31) | 08 |
%H | Two-digit hour based on 24-hour clock (00–23) | 00 |
%I | Two-digit hour based on 12-hour clock (01–12) | 12 |
%M | Two-digit minutes (00–59) | 34 |
%S | Two-digit seconds (00–59) | 59 |
%p | AM or PM | AM |
%L | Three-digit milliseconds (000–999) | 000 |
%f | Six-digit microseconds (000000–999999) | 000000 |
%% | The percent sign | % |
Note: Not all format patterns are supported by all databases.
Learn more
from_unixtime()
Syntax
from_unixtime(number)
Description
Converts a Unix timestamp (seconds since epoch) to a datetime value.
Tags
Function, Time Intelligence Function
Examples
from_unixtime(1672531200)
Inputs
- number (Number: required): The Unix timestamp to convert
Output
- Datetime: The corresponding datetime value
Learn more
last_day()
Syntax
last_day(datetime, date_part)
Description
Returns the last day of the period for a given date.
Tags
Function, Time Intelligence Function
Examples
last_day(orders.created_at, 'month')
last_day(orders.created_at, 'quarter')
Inputs
- datetime (Datetime: required): The date to extract the last day of the period from
- date_part (Text: required): The string to specify the period of the date to return the last day of. Can be one of
'month'
,'year'
,'quarter'
,'week'
.
Output
- Date: The last day of the period for the given date
Learn more
age()
Syntax
age(datetime)
datetime | age()
Description
Returns the age in years.
Tags
Function, Time Intelligence Function
Examples
age(users.registration_date)
users.birthdate | age()
Inputs
- datetime (Datetime: required): The datetime to calculate the age from
Output
- Number: The age in years
Learn more
Table Function
select()
Syntax
select(table, field1, [field2, ...])
table | select(field1, [field2, ...])
Description
Returns a table containing only the specified fields. The select
function serves two main purposes: extracting specific columns from a table and creating calculated fields for use with other table functions (such as sum
, filter
, etc.). When defining a new calculated field, you must assign a name using the syntax field_name: expression
.
Tags
Function, Table Function
Examples
users
| select(users.id, users.email)
users
| select(full_name: concat(users.first_name, " ", users.last_name))
order_items
| select(value: order_items.quantity * products.price)
| sum(value)
Inputs
- table (Table: required): The source table to select fields from.
- field1 (Any: required): The first field to select from the table.
- [field2, ...] (Any: optional, repeatable): Additional fields to select from the table.
Output
- Table: A new table containing only the specified fields.
Learn more
group()
Syntax
group(table, dimension1, [dimension2, ...])
table | group(dimension1, [dimension2, ...])
Description
Returns a table grouped by one or more specified dimensions. The group
function aggregates data by the specified dimensions and creates a grouped table as input for Nested Aggregation. It must be followed by either select
or filter
.
Note: For most Nested Aggregation cases we actually recommend using unique(dimension) | select(aggregation)
as it's shorter and easier to use.
Tags
Function, Table Function
Examples
users
| group(users.country)
| select(users.country, user_count: count(users.id))
users
| group(users.country)
| filter(count(users.id) > 1000)
users
| group(users.id)
| select(users.id, total_order_value)
| average(total_order_value)
Inputs
- table (Table: required): The input table to group.
- dimension1 (Dimension: required): The first dimension to group by.
- [dimension2, ...] (Dimension: optional, repeatable): Additional dimensions to group by.
Output
- Table: A table grouped by the specified dimensions.
Learn more
filter()
Syntax
filter(table, condition1, [condition2, ...])
table | filter(condition1, [condition2, ...])
Description
Returns a table containing only the rows that satisfy one or more specified conditions. The filter
function narrows down a table based on specific criteria by applying conditions locally to each row of the input table. It can be used before further transformations or aggregations.
Difference vs where: filter
evaluates conditions for each row locally after data is retrieved. In contrast, where
pushes conditions down to the target model before any calculation happens, similar to a Dashboard Filter.
Tags
Function, Table Function, Condition
Examples
users
| filter(users.country == 'Canada')
users
| filter(date_diff('day', users.created_at, users.first_buy_at) <= 3)
users
| group(users.id)
| filter(sum(orders.value) > 1000)
Inputs
- table (Table: required): The input table expression to filter.
- condition1 (Truefalse: required): The first condition to apply in the filter.
- [condition2, ...] (Truefalse: optional, repeatable): Additional filter conditions.
Output
- Table: A table containing only rows that meet the specified conditions.
Learn more
unique()
Syntax
unique(dimension1, [dimension2, ...])
Description
Returns a table with all unique combinations of the specified dimensions. The unique
function identifies distinct values in one or more fields and creates a table suitable for grouping in Nested Aggregation. It is recommended over group
for most Nested Aggregation use cases.
Tags
Function, Table Function
Examples
unique(users.country)
unique(users.id)
| select(total_order_value)
| average()
Inputs
- dimension1 (Dimension: required): The dimension to get unique values from.
- [dimension2, ...] (Dimension: optional, repeatable): Additional dimensions to get unique values from.
Output
- Table: A table containing unique combinations of the specified dimensions.
Learn more
top()
Syntax
top(n, dimension, by: metric, [logic])
Description
Returns the top N values of a specified dimension based on a metric. The top
function sorts rows in descending order by the specified metric. By default, it uses 'skip' ranking logic (similar to the rank
function), but can use 'dense' ranking logic when specified with logic: 'dense'
.
Tags
Function, Table Function
Examples
top(5, users.name, by: count(orders.id))
top(5, users.name, by: count(orders.id), logic: 'dense')
top(5, users.name, by: count(orders.id))
| select(average(users.age))
Inputs
- n (Number: required): The number of top values to return.
- dimension (Dimension: required): The dimension used to determine the top values.
- by (Any: required): The metric to rank by.
- logic (Text: optional): The ranking logic ('skip' or 'dense'). Default is 'skip'.
Output
- Table: A new table with one row for each top value of the specified dimension.
Learn more
bottom()
Syntax
bottom(n, dimension, by: metric, [logic])
Description
Returns the bottom N values of a specified dimension based on a metric. The bottom
function sorts rows in ascending order by the specified metric. By default, it uses 'skip' ranking logic (similar to the rank
function), but can use 'dense' ranking logic when specified with logic: 'dense'
.
Tags
Function, Table Function
Examples
bottom(5, users.name, by: count(orders.id))
bottom(5, users.name, by: count(orders.id), logic: 'dense')
bottom(5, users.name, by: count(orders.id))
| select(average(users.age))
Inputs
- n (Number: required): The number of bottom values to return.
- dimension (Dimension: required): The dimension used to determine the bottom values.
- by (Any: required): The metric to rank by.
- logic (Text: optional): The ranking logic ('skip' or 'dense'). Default is 'skip'.
Output
- Table: A new table with one row for each bottom value of the specified dimension.
Learn more
Window Function
rank()
Syntax
rank(order: order_expr, ..., [partition: partition_expr, ...])
Description
Returns the rank of rows within a partition of a table. Tie values are assigned the same rank. The next rank in the sequence is not consecutive. For example: 1, 1, 3, 4, 4, 6, ...
Tags
Function, Metric Function, Window Function
Examples
rank(order: count(orders.id) | desc())
Inputs
- order (Any: required, repeatable): A field used for ordering rows within partitions.
- partition (Any: optional, repeatable): A field used for partitioning the table.
Output
- Number: The rank of the current row within its partition.
Learn more
dense_rank()
Syntax
dense_rank(order: order_expr, ..., [partition: partition_expr, ...])
Description
Returns the rank of rows within a partition of a table. Tie values are assigned the same rank. The next rank in the sequence is consecutive. For example: 1, 1, 2, 3, 3, 4, ...
Tags
Function, Metric Function, Window Function
Examples
dense_rank(order: count(orders.id) | desc())
Inputs
- order (Any: required, repeatable): A field used for ordering rows within partitions.
- partition (Any: optional, repeatable): A field used for partitioning the table.
Output
- Number: The dense rank of the current row within its partition.
Learn more
percent_rank()
Syntax
percent_rank(order: order_expr, ..., [partition: partition_expr, ...])
Description
Returns the relative percentile of a row within a partition of a table. The value is between 0 and 1, inclusive.
Tags
Function, Metric Function, Window Function
Examples
percent_rank(order: count(orders.id) | desc())
Inputs
- order (Any: required, repeatable): A field used for ordering rows within partitions.
- partition (Any: optional, repeatable): A field used for partitioning the table.
Output
- Number: The percentile rank of the current row within its partition.
Learn more
ntile()
Syntax
ntile(ranks, order: order_expr, ..., [partition: partition_expr, ...])
Description
Divides the rows within a partition into a specified number of ranked groups.
Tags
Function, Metric Function, Window Function
Examples
ntile(4, order: count(orders.id) | desc())
Inputs
- ranks (Number: required): The number of ranked groups to divide the rows into.
- order (Any: required, repeatable): A field used for ordering rows within partitions.
- partition (Any: optional, repeatable): A field used for partitioning the table.
Output
- Number: The rank group (or bucket number) the current row belongs to, between 1 and
ranks
.
Learn more
next()
Syntax
next(expr, [offset], order: order_expr, ..., [partition: partition_expr, ...])
Description
Returns the value from a following row at a specified offset relative to the current row.
Tags
Function, Metric Function, Window Function, Moving Calculation
Examples
next(count(users.id), order: count(users.id) | desc())
Inputs
- expr (Any: required): The value you want to retrieve from the next row.
- offset (Number: optional): The number of rows ahead to look. Default is 1.
- order (Any: required, repeatable): A field used for ordering rows within partitions.
- partition (Any: optional, repeatable): A field used for partitioning the table. Alias:
reset
.
Output
- Any: The value of the next row at the specified offset relative to the current row.
Learn more
previous()
Syntax
previous(expr, [offset], order: order_expr, ..., [partition: partition_expr, ...])
Description
Returns the value from a preceding row at a specified offset relative to the current row.
Tags
Function, Metric Function, Window Function, Moving Calculation
Examples
previous(count(users.id), order: count(users.id) | desc())
Inputs
- expr (Any: required): The value you want to retrieve from the previous row.
- offset (Number: optional): The number of rows back to look. Default is 1.
- order (Any: required, repeatable): A field used for ordering rows within partitions.
- partition (Any: optional, repeatable): A field used for partitioning the table. Alias:
reset
.
Output
- Any: The value of the previous row at the specified offset relative to the current row.
Learn more
first_value()
Syntax
first_value(expr, order: order_expr, ..., [partition: partition_expr, ...])
Description
Returns the value of an expression from the first row of the window frame.
Tags
Function, Metric Function, Window Function
Examples
first_value(count(orders.id), order: users.created_at | asc())
Inputs
- expr (Any: required): The expression to retrieve the first value from.
- order (Any: required, repeatable): A field used for ordering rows within partitions.
- partition (Any: optional, repeatable): A field used for partitioning the table.
Output
- Any: The value of the expression from the first row of the window frame.
Learn more
last_value()
Syntax
last_value(expr, order: order_expr, ..., [partition: partition_expr, ...])
Description
Returns the value of an expression from the last row of the window frame.
Tags
Function, Metric Function, Window Function
Examples
last_value(count(orders.id), order: users.created_at | asc())
Inputs
- expr (Any: required): The expression to retrieve the last value from.
- order (Any: required, repeatable): A field used for ordering rows within partitions.
- partition (Any: optional, repeatable): A field used for partitioning the table.
Output
- Any: The value of the expression from the last row of the window frame.
Learn more
nth_value()
Syntax
nth_value(expr, index, order: order_expr, ..., [partition: partition_expr, ...])
Description
Returns the value of an expression from the Nth row of the window frame, where N is a positive integer.
Tags
Function, Metric Function, Window Function
Examples
nth_value(count(orders.id), 2, order: users.created_at | asc())
Inputs
- expr (Any: required): The expression to retrieve the Nth value from.
- index (Number: required): The index of the row from which to retrieve the value.
- order (Any: required, repeatable): A field used for ordering rows within partitions.
- partition (Any: optional, repeatable): A field used for partitioning the table.
Output
- Any: The value of the expression from the Nth row of the window frame.
Learn more
window_sum()
Syntax
window_sum(aggregation_expression, [range], [order: order_expression, ...], [partition: partition_expression, ...])
Description
Returns the sum of values in rows within a specified range relative to the current row.
Tags
Function, Metric Function, Window Function, Cumulative Calculation, Moving Calculation
Examples
window_sum(count(users.id))
window_sum(
count(users.id),
order: count(users.id) | desc()
)
window_sum(
count(users.id),
-2..2,
order: users.created_at | month()
)
window_sum(
count(users.id),
order: users.created_at | month(),
partition: users.gender
)
Inputs
- aggregation_expression (Any: required): An aggregation expression to be summed.
- range (Range: optional): A range of rows to include in the sum. Default is
..0
iforder
is specified, else..
. - order (Any: optional, repeatable): A field used for ordering rows within partitions.
- partition (Any: optional, repeatable): A field used for partitioning the table. Alias:
reset
.
Output
- Number: The sum of the rows within the specified range.
Learn more
window_count()
Syntax
window_count(aggregation_expression, [range], [order: order_expression, ...], [partition: partition_expression, ...])
Description
Returns the count of values in rows within a specified range relative to the current row.
Tags
Function, Metric Function, Window Function, Cumulative Calculation, Moving Calculation
Examples
window_count(
case(
when: sum(orders.value) > 1000000,
then: 1,
else: null
)
)
window_count(
case(
when: sum(orders.value) > 1000000,
then: 1,
else: null
),
order: sum(orders.value) | desc()
)
Inputs
- aggregation_expression (Any: required): An aggregation expression that we want to count.
- range (Range: optional): A range of rows to include in the count. Default is
..0
iforder
is specified, else..
. - order (Any: optional, repeatable): A field used for ordering rows within partitions.
- partition (Any: optional, repeatable): A field used for partitioning the table.
Output
- Number: The count of the rows within the specified range.
Learn more
window_min()
Syntax
window_min(aggregation_expression, [range], [order: order_expression, ...], [partition: partition_expression, ...])
Description
Returns the min of rows in a range relative to the current row.
Tags
Function, Metric Function, Window Function, Cumulative Calculation, Moving Calculation
Examples
window_min(count(users.id))
window_min(
count(users.id),
order: count(users.id) | desc()
)
window_min(
count(users.id),
-2..2,
order: users.created_at | month()
)
window_min(
count(users.id),
order: users.created_at | month(),
partition: users.gender
)
Inputs
- aggregation_expression (Any: required): An aggregation expression that we want to find min of.
- range (Range: optional): A range of rows to include in the min. Default is
..0
iforder
is specified, else..
. - order (Any: optional, repeatable): A field used for ordering rows within partitions.
- partition (Any: optional, repeatable): A field used for partitioning the table.
Output
- Number: The minimum value within the specified range of rows.
Learn more
window_max()
Syntax
window_max(agg_expr, [range], [order: order_expr, ...], [partition: partition_expr, ...])
Description
Returns the maximum value in rows within a specified range relative to the current row.
Tags
Function, Metric Function, Window Function, Cumulative Calculation, Moving Calculation
Examples
window_max(count(users.id))
window_max(
count(users.id),
order: count(users.id) | desc()
)
window_max(
count(users.id),
-2..2,
order: users.created_at | month()
)
window_max(
count(users.id),
order: users.created_at | month(),
partition: users.gender
)
Inputs
- agg_expr (Any: required): An aggregation expression that we want to find max of.
- range (Range: optional): A range of rows to include in the max. Default is
..0
iforder
is specified, else..
. - order (Any: optional, repeatable): A field used for ordering rows within partitions.
- partition (Any: optional, repeatable): A field used for partitioning the table.
Output
- Number: The maximum value within the specified range of rows.
Learn more
window_avg()
Syntax
window_avg(agg_expr, [range], [order: order_expr, ...], [partition: partition_expr, ...])
Description
Returns the average of values in rows within a specified range relative to the current row.
Tags
Function, Metric Function, Window Function, Cumulative Calculation, Moving Calculation
Examples
window_avg(sum(orders.value))
Inputs
- agg_expr (Any: required): An aggregation expression to be averaged.
- range (Range: optional): A range of rows to include in the average. Default is
..0
iforder
is specified, else..
. - order (Any: optional, repeatable): A field used for ordering rows within partitions.
- partition (Any: optional, repeatable): A field used for partitioning the table.
Output
- Number: The average of values within the specified range of rows.
Learn more
window_stdev()
Syntax
window_stdev(agg_expr, [range], [order: order_expr, ...], [partition: partition_expr, ...])
Description
Returns the sample standard deviation of values in rows within a specified range relative to the current row.
Tags
Function, Metric Function, Window Function, Cumulative Calculation, Moving Calculation
Examples
window_stdev(sum(orders.value))
Inputs
- agg_expr (Any: required): An aggregation expression that we want to find the (sample) standard deviation of.
- range (Range: optional): A range of rows to include in the (sample) standard deviation. Default is
..0
iforder
is specified, else..
. - order (Any: optional, repeatable): A field used for ordering rows within partitions.
- partition (Any: optional, repeatable): A field used for partitioning the table.
Output
- Number: The sample standard deviation of values within the specified range of rows.
Learn more
window_stdevp()
Syntax
window_stdevp(agg_expr, [range], [order: order_expr, ...], [partition: partition_expr, ...])
Description
Returns the population standard deviation of values in rows within a specified range relative to the current row.
Tags
Function, Metric Function, Window Function, Cumulative Calculation, Moving Calculation
Examples
window_stdevp(sum(orders.value))
Inputs
- agg_expr (Any: required): An aggregation expression that we want to find the (population) standard deviation of.
- range (Range: optional): A range of rows to include in the (population) standard deviation. Default is
..0
iforder
is specified, else..
. - order (Any: optional, repeatable): A field used for ordering rows within partitions.
- partition (Any: optional, repeatable): A field used for partitioning the table.
Output
- Number: The population standard deviation of values within the specified range of rows.
Learn more
window_var()
Syntax
window_var(agg_expr, [range], [order: order_expr, ...], [partition: partition_expr, ...])
Description
Returns the sample variance of values in rows within a specified range relative to the current row.
Tags
Function, Metric Function, Window Function, Cumulative Calculation, Moving Calculation
Examples
window_var(sum(orders.value))
Inputs
- agg_expr (Any: required): An aggregation expression that we want to find the (sample) variance of.
- range (Range: optional): A range of rows to include in the (sample) variance. Default is
..0
iforder
is specified, else..
. - order (Any: optional, repeatable): A field used for ordering rows within partitions.
- partition (Any: optional, repeatable): A field used for partitioning the table.
Output
- Number: The sample variance of values within the specified range of rows.
Learn more
window_varp()
Syntax
window_varp(agg_expr, [range], [order: order_expr, ...], [partition: partition_expr, ...])
Description
Returns the population variance of values in rows within a specified range relative to the current row.
Tags
Function, Metric Function, Window Function, Cumulative Calculation, Moving Calculation
Examples
window_varp(sum(orders.value))
Inputs
- agg_expr (Any: required): An aggregation expression that we want to find the (population) variance of.
- range (Range: optional): A range of rows to include in the (population) variance. Default is
..0
iforder
is specified, else..
. - order (Any: optional, repeatable): A field used for ordering rows within partitions.
- partition (Any: optional, repeatable): A field used for partitioning the table.
Output
- Number: The population variance of values within the specified range of rows.
Learn more
Relationship Function
with_relationships()
Syntax
with_relationships(metric, relationship1, [relationship2, ...])
metric | with_relationships(relationship1, [relationship2, ...])
Description
Calculates a metric using specific relationships that are not active by default. The with_relationships
function activates inactive model relationships for a specific metric calculation. It is commonly used with role-playing dimensions or fact constellation schemas and enables metrics that require specific join paths.
Tags
Function, Metric Function, Relationship Function
Examples
count(orders.id)
| with_relationships(orders.delivered_at > dim_dates.date)
sum(orders.value)
| with_relationships(
orders.created_at > dim_dates.date,
orders.delivered_at > dim_dates.date
)
Inputs
- metric (Any: required): The original metric to which the specified relationship(s) will be applied.
- relationship1 (Relationship: required): The first relationship to apply to the metric calculation.
- [relationship2, ...] (Relationship: optional, repeatable): Additional relationships to apply.
Output
- Any: The original metric calculation using specified relationships.
Learn more
Null/Zero Handling Function
coalesce()
Syntax
coalesce(val1, val2, ...)
Description
Returns the first non-null value in a list of expressions. This function is commonly used to handle missing data by providing fallback values.
Tags
Function, Null/Zero Handling Function
Examples
coalesce(users.yearly_payment, users.quarterly_payment, 0)
Inputs
- val1 (Any: required): The first value to check.
- val2 (Any: optional, repeatable): Subsequent values to check if previous values are null.
Output
- Any: The first non-null value in the arguments.
Learn more
nullif()
Syntax
nullif(val1, val2)
Description
Returns NULL if two expressions are equal, otherwise returns the first expression. This function is primarily used to avoid divide-by-zero errors in calculations.
Tags
Function, Null/Zero Handling Function
Examples
sales_current / nullif(sales_target, 0)
Inputs
- val1 (Any: required): The first value to compare.
- val2 (Any: required): The second value to compare.
Output
- Any: NULL if values are equal, otherwise val1.
Learn more
safe_divide()
Syntax
safe_divide(dividend, divisor)
Description
Returns the division with a safe mechanism to handle division by zero. Returns NULL if the divisor is zero, preventing potential errors in calculations.
Tags
Function, Null/Zero Handling Function
Examples
safe_divide(total_sales, count_orders)
Inputs
- dividend (Number: required): The number to be divided.
- divisor (Number: required): The number to divide by.
Output
- Number: The result of the division, or NULL if the divisor is zero.
Learn more
Mathematical Function
abs()
Syntax
abs(number)
Description
Returns the absolute value of a number, removing any negative sign and returning the non-negative magnitude.
Tags
Function, Mathematical Function
Examples
abs(-1) // Returns 1
abs(5) // Returns 5
Inputs
- number (Number: required): The number to get the absolute value of.
Output
- Number: The non-negative magnitude of the input number.
Learn more
sqrt()
Syntax
sqrt(number)
Description
Calculates the square root of a given number.
Tags
Function, Mathematical Function
Examples
sqrt(9) // Returns 3
sqrt(10) // Returns approximately 3.1623
Inputs
- number (Number: required): The number to calculate the square root of.
Output
- Number: The square root of the input number.
Learn more
ceil()
Syntax
ceil(number)
Description
Returns the smallest integer greater than or equal to the given number.
Tags
Function, Mathematical Function
Examples
ceil(1.1) // Returns 2
ceil(1.9) // Returns 2
Inputs
- number (Number: required): The number to round up.
Output
- Number: The smallest integer greater than or equal to the input number.
Learn more
floor()
Syntax
floor(number)
Description
Returns the largest integer less than or equal to the given number.
Tags
Function, Mathematical Function
Examples
floor(1.1) // Returns 1
floor(1.9) // Returns 1
Inputs
- number (Number: required): The number to round down.
Output
- Number: The largest integer less than or equal to the input number.
Learn more
round()
Syntax
round(number, [scale])
Description
Rounds a number to a specified number of decimal places. By default, rounds to the nearest whole number.
Tags
Function, Mathematical Function
Examples
round(1.1) // Returns 1
round(1.9) // Returns 2
round(1.12345, 2) // Returns 1.12
round(-1.5) // Returns -2
Inputs
- number (Number: required): The number to round.
- scale (Number: optional): The number of decimal places to round to. Default is 0.
Output
- Number: The rounded number.
Learn more
trunc()
Syntax
trunc(number, [scale])
Description
Truncates a number to a specified number of decimal places, removing digits beyond the specified scale.
Tags
Function, Mathematical Function
Examples
trunc(1.1) // Returns 1
trunc(1.9) // Returns 1
trunc(1.12345, 2) // Returns 1.12
Inputs
- number (Number: required): The number to truncate.
- scale (Number: optional): The number of decimal places to keep. Default is 0.
Output
- Number: The truncated number.
Learn more
exp()
Syntax
exp(number)
Description
Returns the value of the mathematical constant e (Euler's number) raised to the power of the given number.
Tags
Function, Mathematical Function
Examples
exp(1) // Returns approximately 2.718
Inputs
- number (Number: required): The exponent to raise e to.
Output
- Number: The result of e raised to the given power.
Learn more
ln()
Syntax
ln(number)
Description
Calculates the natural logarithm (base e) of a given number.
Tags
Function, Mathematical Function
Examples
ln(1) // Returns 0
ln(e()) // Returns 1
Inputs
- number (Number: required): The number to calculate the natural logarithm of.
Output
- Number: The natural logarithm of the input number.
Learn more
log10()
Syntax
log10(number)
Description
Calculates the base 10 logarithm of a given number.
Tags
Function, Mathematical Function
Examples
log10(100) // Returns 2
log10(10) // Returns 1
Inputs
- number (Number: required): The number to calculate the base 10 logarithm of.
Output
- Number: The base 10 logarithm of the input number.
Learn more
log2()
Syntax
log2(number)
Description
Calculates the base 2 logarithm of a given number.
Tags
Function, Mathematical Function
Examples
log2(8) // Returns 3
log2(16) // Returns 4
Inputs
- number (Number: required): The number to calculate the base 2 logarithm of.
Output
- Number: The base 2 logarithm of the input number.
Learn more
pow()
Syntax
pow(base, exponent)
Description
Raises a base number to the power of an exponent.
Tags
Function, Mathematical Function
Examples
pow(2, 3) // Returns 8
pow(10, 2) // Returns 100
Inputs
- base (Number: required): The base number.
- exponent (Number: required): The power to raise the base to.
Output
- Number: The result of the base raised to the exponent.
Learn more
mod()
Syntax
mod(dividend, divisor)
Description
Returns the remainder of a division operation.
Tags
Function, Mathematical Function
Examples
mod(5, 2) // Returns 1
mod(10, 3) // Returns 1
Inputs
- dividend (Number: required): The number to be divided.
- divisor (Number: required): The number to divide by.
Output
- Number: The remainder of the division.
Learn more
div()
Syntax
div(dividend, divisor)
Description
Returns the integer quotient of a division operation.
Tags
Function, Mathematical Function
Examples
div(5, 2) // Returns 2
div(10, 3) // Returns 3
Inputs
- dividend (Number: required): The number to be divided.
- divisor (Number: required): The number to divide by.
Output
- Number: The integer result of the division.
Learn more
sign()
Syntax
sign(number)
Description
Returns the sign of a number: 1 for positive, -1 for negative, and 0 for zero.
Tags
Function, Mathematical Function
Examples
sign(5) // Returns 1
sign(-5) // Returns -1
sign(0) // Returns 0
Inputs
- number (Number: required): The number to determine the sign of.
Output
- Number: The sign of the input number (-1, 0, or 1).
Learn more
radians()
Syntax
radians(degrees)
Description
Converts degrees to radians.
Tags
Function, Mathematical Function
Examples
radians(180) // Returns approximately 3.14159 (π)
Inputs
- degrees (Number: required): The angle in degrees to convert.
Output
- Number: The angle converted to radians.
Learn more
degrees()
Syntax
degrees(radians)
Description
Converts radians to degrees.
Tags
Function, Mathematical Function
Examples
degrees(pi()) // Returns 180
Inputs
- radians (Number: required): The angle in radians to convert.
Output
- Number: The angle converted to degrees.
Learn more
pi()
Syntax
pi()
Description
Returns the mathematical constant π (pi).
Tags
Function, Mathematical Function
Examples
pi() // Returns approximately 3.14159
Inputs
Output
- Number: The value of π.
Learn more
acos()
Syntax
acos(number)
Description
Returns the arccosine (inverse cosine) of a number.
Tags
Function, Mathematical Function
Examples
acos(cos(pi())) // Returns approximately 3.14159
Inputs
- number (Number: required): The number to calculate the arccosine of.
Output
- Number: The arccosine of the input number.
Learn more
asin()
Syntax
asin(number)
Description
Returns the arcsine (inverse sine) of a number.
Tags
Function, Mathematical Function
Examples
asin(sin(pi() / 2)) // Returns approximately 1.5708
Inputs
- number (Number: required): The number to calculate the arcsine of.
Output
- Number: The arcsine of the input number.
Learn more
atan()
Syntax
atan(number)
Description
Returns the arctangent (inverse tangent) of a number.
Tags
Function, Mathematical Function
Examples
atan(tan(pi() / 4)) // Returns approximately 0.7854
Inputs
- number (Number: required): The number to calculate the arctangent of.
Output
- Number: The arctangent of the input number.
Learn more
atan2()
Syntax
atan2(y, x)
Description
Returns the two-argument arctangent, which computes the angle between the positive x-axis and the point given by the coordinates (x, y).
Tags
Function, Mathematical Function
Examples
atan2(2, pi()) // Returns a specific angle
Inputs
- y (Number: required): The y-coordinate.
- x (Number: required): The x-coordinate.
Output
- Number: The angle in radians.
Learn more
cos()
Syntax
cos(number)
Description
Returns the cosine of a number.
Tags
Function, Mathematical Function
Examples
cos(pi()) // Returns -1
Inputs
- number (Number: required): The angle in radians.
Output
- Number: The cosine of the input number.
Learn more
sin()
Syntax
sin(number)
Description
Returns the sine of a number.
Tags
Function, Mathematical Function
Examples
sin(pi() / 2) // Returns 1
Inputs
- number (Number: required): The angle in radians.
Output
- Number: The sine of the input number.
Learn more
tan()
Syntax
tan(number)
Description
Returns the tangent of a number.
Tags
Function, Mathematical Function
Examples
tan(pi() / 4) // => 1
Inputs
- number (Number: required): The angle in radians.
Output
- Number: The tangent of the input number.
Learn more
cot()
Syntax
cot(number)
Description
Returns the cotangent of a number.
Tags
Function, Mathematical Function
Examples
cot(pi() / 4) // => 1
Inputs
- number (Number: required): The angle in radians.
Output
- Number: The cotangent of the input number.
Learn more
AI Function
AI functions are only available on Databricks and Snowflake data platforms. These functions leverage the native AI capabilities provided by these platforms and are not supported on other database systems.
ai_query()
Syntax
ai_query(model, prompt)
Description
Queries an AI model with a text prompt and returns the generated response.
Tags
Function, AI Function
Examples
ai_query('databricks-meta-llama-3-3-70b-instruct', 'What is Apache Spark?')
orders
| group(orders.product_category)
| select(
category: orders.product_category,
total_sales: sum(orders.revenue),
ai_insight: ai_query('gpt-4', concat('Analyze sales trend for ', orders.product_category))
)
Inputs
- model (Text: required): The AI model to use
- prompt (Text: required): The text prompt for the AI model
Output
- Text: The AI-generated response
Learn more
ai_similarity()
Syntax
ai_similarity(text1, text2)
Description
Calculates the semantic similarity between two text strings.
Tags
Function, AI Function
Examples
ai_similarity('Apache Spark', 'Apache Spark') // => 1.0
products
| select(
product_name: products.name,
similarity_score: ai_similarity(products.description, 'data analytics platform')
)
| filter(similarity_score > 0.7)
Inputs
- text1 (Text: required): The first text to compare
- text2 (Text: required): The second text to compare
Output
- Number: Similarity score between 0 and 1
Learn more
ai_classify()
Syntax
ai_classify(text, ...categories)
Description
Classifies text into one of the provided categories using AI.
Tags
Function, AI Function
Examples
ai_classify('My password is leaked.', 'urgent', 'not urgent') // => 'urgent'
support_tickets
| select(
ticket_id: support_tickets.id,
message: support_tickets.message,
category: ai_classify(support_tickets.message, 'technical', 'billing', 'general')
)
Inputs
- text (Text: required): The text to classify
- categories (Text: required): Variable number of category options
Output
- Text: The selected category
Learn more
ai_summarize()
Syntax
ai_summarize(content)
Description
Generates a concise summary of the provided text content using AI.
Tags
Function, AI Function
Examples
ai_summarize('Long article about climate change...')
product_reviews
| group(product_reviews.product_id)
| select(
product_id: product_reviews.product_id,
review_summary: ai_summarize(string_agg(product_reviews.review_text, sep: ';'))
)
Inputs
- content (Text: required): The text content to summarize
Output
- Text: The generated summary
Learn more
Miscellaneous Function
cast()
Syntax
cast(expr, type)
Description
Returns the input value casted to the specified data type.
Tags
Function, Miscellaneous Function
Examples
cast('2000', 'int')
Inputs
- expr (Any: required): The expression to cast.
- type (Text: required): The target data type. Valid options include: 'text', 'number', 'int'/'integer', 'date', 'datetime', 'truefalse'.
Output
- Any: the value cast to the specified type.
Learn more
concat()
Syntax
concat(text, [text ...])
Description
Returns the concatenated string of multiple strings.
Tags
Function, Miscellaneous Function
Examples
concat('Hello', ' ', 'World')
Inputs
- text (Text: required, repeatable): The strings to concatenate.
Output
- Text: The concatenated string.
Learn more
find()
Syntax
find(text, substring)
Description
Returns the 1-based index of the first occurrence of a substring within a text string. Returns 0 if the substring is not found.
Tags
Function, Miscellaneous Function
Examples
find('Hello World', 'World') // Returns 7
find('Hello World', 'o') // Returns 5
find('Hello World', 'Universe') // Returns 0
Inputs
- text (Text: required): The text string to search within.
- substring (Text: required): The substring to search for.
Output
- Number: The 1-based index of the first occurrence of the substring, or 0 if not found.
Aliases
find_index()
Learn more
left()
Syntax
left(text, length)
Description
Returns the leftmost characters of a text string, up to the specified length.
Tags
Function, Miscellaneous Function
Examples
left('Hello World', 5) // Returns 'Hello'
left('Hello', 10) // Returns 'Hello'
Inputs
- text (Text: required): The text string to extract from.
- length (Number: required): The number of characters to extract from the left.
Output
- Text: The leftmost characters of the text string.
Learn more
right()
Syntax
right(text, length)
Description
Returns the rightmost characters of a text string, up to the specified length.
Tags
Function, Miscellaneous Function
Examples
right('Hello World', 5) // Returns 'World'
right('Hello', 10) // Returns 'Hello'
Inputs
- text (Text: required): The text string to extract from.
- length (Number: required): The number of characters to extract from the right.
Output
- Text: The rightmost characters of the text string.
Learn more
mid()
Syntax
mid(text, start, length)
Description
Extracts a substring of a specified length from a text string, starting at a given position (1-based).
Tags
Function, Miscellaneous Function
Examples
mid('Hello World', 7, 5) // Returns 'World'
mid('Hello World', 3, 3) // Returns 'llo'
Inputs
- text (Text: required): The text string to extract from.
- start (Number: required): The starting position of the substring (1-based).
- length (Number: required): The length of the substring to extract.
Output
- Text: The extracted substring.
Learn more
len()
Syntax
len(text)
Description
Returns the length of a text string (number of characters).
Tags
Function, Miscellaneous Function
Examples
len('Hello World') // Returns 11
len('') // Returns 0
Inputs
- text (Text: required): The text string to calculate the length of.
Output
- Number: The length of the text string.
Learn more
lower()
Syntax
lower(text)
Description
Converts a text string to lowercase.
Tags
Function, Miscellaneous Function
Examples
lower('Hello World') // Returns 'hello world'
lower('HELLO') // Returns 'hello'
Inputs
- text (Text: required): The text string to convert.
Output
- Text: The lowercase version of the text string.
Learn more
upper()
Syntax
upper(text)
Description
Converts a text string to uppercase.
Tags
Function, Miscellaneous Function
Examples
upper('Hello World') // Returns 'HELLO WORLD'
upper('hello') // Returns 'HELLO'
Inputs
- text (Text: required): The text string to convert.
Output
- Text: The uppercase version of the text string.
Learn more
trim()
Syntax
trim(text)
Description
Removes leading and trailing whitespace from a text string.
Tags
Function, Miscellaneous Function
Examples
trim(' Hello World ') // Returns 'Hello World'
trim(' Hello ') // Returns 'Hello'
Inputs
- text (Text: required): The text string to trim.
Output
- Text: The text string with leading and trailing whitespace removed.
Learn more
ltrim()
Syntax
ltrim(text)
Description
Removes leading whitespace from a text string.
Tags
Function, Miscellaneous Function
Examples
ltrim(' Hello World') // Returns 'Hello World'
ltrim(' Hello ') // Returns 'Hello '
Inputs
- text (Text: required): The text string to trim.
Output
- Text: The text string with leading whitespace removed.
Learn more
rtrim()
Syntax
rtrim(text)
Description
Removes trailing whitespace from a text string.
Tags
Function, Miscellaneous Function
Examples
rtrim('Hello World ') // Returns 'Hello World'
rtrim(' Hello ') // Returns ' Hello'
Inputs
- text (Text: required): The text string to trim.
Output
- Text: The text string with trailing whitespace removed.
Learn more
lpad()
Syntax
lpad(text, length, pad_string)
Description
Pads the left side of a text string with a specified pad string until it reaches the specified length. If the text is already longer than the length it will be truncated.
Tags
Function, Miscellaneous Function
Examples
lpad('Hello', 10, ' ') // Returns ' Hello'
lpad('Hello', 10, 'abc') // Returns 'abcabHello'
lpad('Hello World', 10, '0') // Returns 'Hello Worl'
Inputs
- text (Text: required): The text string to pad.
- length (Number: required): The desired length of the padded string.
- pad_string (Text: required): The string to use for padding.
Output
- Text: The padded text string.
Learn more
rpad()
Syntax
rpad(text, length, pad_string)
Description
Pads the right side of a text string with a specified pad string until it reaches the specified length. If the text is already longer than the length it will be truncated.
Tags
Function, Miscellaneous Function
Examples
rpad('Hello', 10, ' ') // Returns 'Hello '
rpad('Hello', 10, 'abc') // Returns 'Helloabcab'
rpad('Hello World', 10, '0') // Returns 'Hello Worl'
Inputs
- text (Text: required): The text string to pad.
- length (Number: required): The desired length of the padded string.
- pad_string (Text: required): The string to use for padding.
Output
- Text: The padded text string.
Learn more
replace()
Syntax
replace(text, old_substring, new_substring)
Description
Replaces all occurrences of a substring within a text string with a new substring.
Tags
Function, Miscellaneous Function
Examples
replace('Hello World', 'World', 'Universe') // Returns 'Hello Universe'
replace('Hello World', 'o', '0') // Returns 'Hell0 W0rld'
Inputs
- text (Text: required): The text string to perform the replacement in.
- old_substring (Text: required): The substring to replace.
- new_substring (Text: required): The substring to replace with.
Output
- Text: The text string with the replacements made.
Learn more
split_part()
Syntax
split_part(text, delimiter, part_number)
Description
Splits a text string into parts based on a delimiter and returns the specified part (1-based).
Tags
Function, Miscellaneous Function
Examples
split_part('apple,banana,cherry', ',', 2) // Returns 'banana'
split_part('Hello World', ' ', 1) // Returns 'Hello'
Inputs
- text (Text: required): The text string to split.
- delimiter (Text: required): The delimiter to split the string by.
- part_number (Number: required): The 1-based index of the part to return.
Output
- Text: The specified part of the split string.
Learn more
regexp_extract()
Syntax
regexp_extract(text, regex, [occurrence], [group: _group], [flags: _flags])
Description
Extracts a substring from a text string that matches a regular expression pattern.
Tags
Function, Miscellaneous Function
Examples
regexp_extract('Product123', '[0-9]+') // Returns '123'
regexp_extract('Hello World Example', '\\w+', 2) // Returns 'World'
regexp_extract('Hello World', 'hello', flags: 'i') // Returns 'Hello'
regexp_extract('Product123.3', '(\\d+)\\.\\d+', group: 1) // Returns '123'
Inputs
- text (Text: required): The text string to search within.
- regex (Text: required): The regular expression pattern to match. (exact regex syntax depends on the database)
- occurrence (Number: optional): The occurrence of the matching pattern to extract. (1-based index)
- group (Number: optional): The capture group to extract from the matched occurrence. (Bigquery does not support this)
- flags (Text: optional): Flags to modify the behavior of the regular expression matching. (supported flags depends on the database)
Output
- Text: The extracted substring, or NULL if no match is found.
Learn more
regexp_like()
Syntax
regexp_like(text, regex, [flags: _flags])
Description
Checks if a text string matches a regular expression pattern.
Tags
Function, Miscellaneous Function
Examples
regexp_like('Product123', '[0-9]+') // Returns true
regexp_like('Hello World', '^hello', flags: 'i') // Returns true
Inputs
- text (Text: required): The text string to search within.
- regex (Text: required): The regular expression pattern to match. (exact regex syntax depends on the database)
- flags (Text: optional): Flags to modify the behavior of the regular expression matching. (supported flags depends on the database)
Output
- Truefalse: True if the text string matches the regular expression pattern, otherwise false.
Learn more
regexp_replace()
Syntax
regexp_replace(text, regex, substitute, [flags: _flags])
Description
Replaces substrings in a text that match a regular expression pattern with a specified replacement text.
Tags
Function, Miscellaneous Function
Examples
regexp_replace('Product123', '[0-9]+', 'X') // Returns 'ProductX'
regexp_replace('Hello World', '\\s+', ' ') // Returns 'Hello World'
regexp_replace('John Doe', '(\\w+) (\\w+)', '\\2, \\1') // Returns 'Doe, John'
Inputs
- text (Text: required): The text string to perform the replacement in.
- regex (Text: required): The regular expression pattern to match. (exact regex syntax depends on the database)
- substitute (Text: required): The replacement string. You can use backreferences like
\\1
or$1
(depending on the specific database) to refer to captured groups in the regex. - flags (Text: optional): Flags to modify the behavior of the regular expression matching. (supported flags depends on the database)
Output
- Text: The text with the replacements made.
Learn more
is_at_level()
Syntax
is_at_level(dimension)
Description
Returns true if the specified dimension is active in the Level of Detail (LoD) context, else false.
Tags
Function, Miscellaneous Function
Examples
case(
when: is_at_level(cities.name),
then: sum(sales.amount) / (sum(sales.amount) | of_all(cities.name)),
when: is_at_level(countries.name),
then: sum(sales.amount) / (sum(sales.amount) | of_all(countries.name)),
else: 1
)
Inputs
- dimension (Dimension: required): The dimension to test.
Output
- Truefalse: Whether the dimension is active in the current LoD context or not