AQL Cheatsheet - Functions

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

AQL Function, Table Function

Examples

Select user ID and email from the users table
users | select(users.id, users.email)

Define a new column full_name for the users table
users | select(full_name: concat(users.first_name, " ", users.last_name))

Calculate total orders value
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

select (AQL Doc)

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

AQL Function, Table Function

Examples

Count the number of users per country
users | group(users.country) | select(users.country, user_count: count(users.id))

Find all countries that have more than 1000 users
users | group(users.country) | filter(count(users.id) > 1000)

Average of (total order value by user ID)
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

group (AQL Doc)

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

AQL Function, Table Function

Examples

Filter users from Canada
users | filter(users.country == 'Canada')

Filter users who buy within 3 days of activation
users | filter(date_diff('day', users.created_at, users.first_buy_at) <= 3)

Filter users with premium orders
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

filter (AQL Doc)

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

AQL Function, Table Function

Examples

Get unique countries from the users table
unique(users.country)

Average of (total order value by user ID)
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

unique (AQL Doc)

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

AQL Function, Table Function

Examples

Get top 5 users based on order count
top(5, users.name, by: count(orders.id))

Get top 5 users using dense ranking
top(5, users.name, by: count(orders.id), logic: 'dense')

Average age of top 5 users by order count
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

top (AQL Doc)

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

AQL Function, Table Function

Examples

Get bottom 5 users based on order count
bottom(5, users.name, by: count(orders.id))

Get bottom 5 users using dense ranking
bottom(5, users.name, by: count(orders.id), logic: 'dense')

Average age of bottom 5 users by order count
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

bottom (AQL Doc)

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

AQL Function, Metric Function, Condition Function

Examples

Calculate total order value for completed orders
total_order_value | where(orders.status == 'completed')

Total value of top 5 buyers by volumn
total_order_value | where(users.id in top(5, users.id, by: count(orders.id)))

Revenue generated in the last 30 days
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

where (AQL Doc)

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

AQL Function, Metric Function, Relationship Function

Examples

Count of orders delivered
count(orders.id) | with_relationships(orders.delivered_at > dim_dates.date)

Calculate the total order value considering both order and delivery dates
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

with_relationships (AQL Doc)

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

AQL Function, Metric Function, Level of Detail Function

Examples

Calculate percent of total across all countries (i.e ignoring country dimension)
order_value / (order_value | of_all(order_items.country))

Count the orders excluding category dimension
count(orders.id) | of_all(orders.category) 

Calculate the delta of order value vs the average across all country
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

of_all (AQL Doc)

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

AQL Function, Metric Function, Level of Detail Function

Aliases

keep()

Examples

Calculate the total spent, keeping users grains
// This keeps the grain of the entire user model during the metric calculation
sum(order_items.order_value) | keep_grains(users)

Average order value calculation, keeping users.id grain
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

keep_grains (AQL Doc)

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

AQL Function, Metric Function, Level of Detail Function

Examples

Use the customer lifetime value as a dimension
sum(orders.amount) | dimensionalize(users.id)

Use the customer cohort as a dimension
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

dimensionalize (AQL Doc)

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

AQL Function, Metric Function, Time-based Function

Examples

Calculate running total of orders along created_at dimension
running_total(orders.total_orders, orders.created_at)

Calculate running total of orders along multiple dimensions
running_total(orders.total_orders, orders.created_at, orders.status)

Calculate running total of orders ignoring filters on running dimensions
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 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

running_total (AQL Doc)

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

AQL Function, Metric Function, Time-based Function

Examples

Calculate the year-to-date total orders
count(orders.id) | period_to_date('year', orders.created_at)

Calculate the month-to-date total orders
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

period_to_date (AQL Doc)

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

AQL Function, Metric Function, Time-based Function

Examples

Calculate the total orders in a specific time range
exact_period(orders.total_orders, orders.created_at, @2022-07-01 - 2022-09-01)

Calculate the total orders in a specific time range (pipe syntax)
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 (Datetimeliteral: 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

exact_period (AQL Doc)

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

AQL Function, Metric Function, Time-based Function

Examples

Calculate the total orders shifted by 1 month
relative_period(orders.total_orders, orders.created_at, interval(-1 month))

Calculate the total orders shifted by 1 month (pipe syntax)
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

relative_period (AQL Doc)

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

AQL Function, Metric Function, Time-based Function

Examples

Calculate the total orders in the last 3 months
trailing_period(count(orders.id), orders.created_at, interval(3 months))

Calculate the total orders in the last 3 months (pipe syntax)
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

trailing_period (AQL Doc)

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

AQL Function, Metric Function, Window Function

Examples

Rank by order count in descending order
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

rank (AQL Doc)

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

AQL Function, Metric Function, Window Function

Examples

Dense rank by order count in descending order
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

dense_rank (AQL Doc)

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

AQL Function, Metric Function, Window Function

Examples

Get the next row's user count in descending order
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

next (AQL Doc)

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

AQL Function, Metric Function, Window Function

Examples

Get the previous row's user count in descending order
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

previous (AQL Doc)

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

AQL Function, Metric Function, Window Function

Examples

Calculate the running total of user count
window_sum(count(users.id))

Calculate the running total of user count with descending order
window_sum(count(users.id), order: count(users.id) | desc())

Calculate running total of user count within a 5-row sliding window with monthly order
window_sum(count(users.id), -2..2, order: users.created_at | month())

Calculate running total of user count by gender within monthly order
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 if order 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_sum (AQL Doc)

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

AQL Function, Metric Function, Window Function

Examples

Calculate the running count of gold membership
window_count(case(
  when: sum(orders.value) > 1000000
  , then: 1
  , else: null
))

Calculate the running count of gold membership after descending order total order value
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 if order 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_count (AQL Doc)

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

AQL Function, Metric Function, Window Function

Examples

Calculate the running minimum user count
window_min(count(users.id))

Calculate the running minimum user count within descending order window
window_min(count(users.id), order: count(users.id) | desc())

Calculate the running minimum user count within a 5-row sliding window and monthly order
window_min(count(users.id), -2..2, order: users.created_at | month())

calculate the running minimum user count by gender within monthly order
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 if order 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_min (AQL Doc)

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

AQL Function, Metric Function, Window Function

Examples

Calculate the running maximum user count
window_max(count(users.id))

Calculate the running maximum user count within descending order window
window_max(count(users.id), order: count(users.id) | desc())

Calculate the running maximum user count within a 5-row sliding window and monthly order
window_max(count(users.id), -2..2, order: users.created_at | month())

calculate the running maximum user count by gender within monthly order
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 if order 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_max (AQL Doc)

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

AQL Function, Metric Function, Window Function

Examples

Calculate the running average of total order value
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 if order 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_avg (AQL Doc)

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

AQL Function, Metric Function, Window Function

Examples

Calculate the sample standard deviation of total order value relative to current row
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 if order 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_stdev (AQL Doc)

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

AQL Function, Metric Function, Window Function

Examples

Calculate the population standard deviation of total order value relative to current row
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 if order 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_stdevp (AQL Doc)

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

AQL Function, Metric Function, Window Function

Examples

Calculate the sample variance of total order value relative to current row
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 if order 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_var (AQL Doc)

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

AQL Function, Metric Function, Window Function

Examples

Calculate the population variance of total order value relative to current row
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 if order 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

window_varp (AQL Doc)

count()

Syntax

count([table], expression)

Description

Counts the total number of items in a group, excluding NULL values.

Tags

AQL Function, Aggregate Function

Examples

Count total orders
orders | count(orders.id)

Count completed orders
orders | count(orders.id) | where(orders.status == 'completed')

Count orders per country
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 (AQL Doc)

count_if()

Syntax

count_if([table], condition)

Description

Counts the total rows from one table that satisfy the given condition.

Tags

AQL Function, Aggregate Function

Examples

Count orders where delivery country is USA
orders | count_if(orders.delivery_country == 'USA')

Filtered Count shipped orders
orders | count_if(orders.status == 'shipped')

Count shipped orders per country
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_if (AQL Doc)

count_distinct()

Syntax

count_distinct([table], expression)

Description

Counts the total number of distinct items in a group, excluding NULL values.

Tags

AQL Function, Aggregate Function

Examples

Count unique customers
orders | count_distinct(orders.customer_id) 

Count unique customers in USA
customers | count_distinct(customers.id) | where(customers.country == 'USA')

Count unique delivery countries per product
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

count_distinct (AQL Doc)

average()

Syntax

average([table], expression)

Description

Calculates the average of values in a group, excluding NULL values.

Tags

AQL Function, Aggregate Function

Examples

Average order value
orders | average(orders.value)

Average order value for completed orders
orders | average(orders.value) | where(orders.status == 'completed')

Average order value per country
orders | group(orders.delivery_country) | select(orders.delivery_country, avg_order_value: average(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

Number: The average of the values.

Aliases

avg()

Learn more

average (AQL Doc)

min()

Syntax

min([table], expression)

Description

Returns the item in the group with the smallest value, excluding NULL values.

Tags

AQL Function, Aggregate Function

Examples

Basic Usage: Minimum order value
orders | min(orders.value)

Get the minimum completed order value
orders | min(orders.value) | where(orders.status == 'completed')

Get the minimum order value per country
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

min (AQL Doc)

max()

Syntax

max([table], expression)

Description

Returns the item in the group with the largest value, excluding NULL values.

Tags

AQL Function, Aggregate Function

Examples

Get the maximum order value
orders | max(orders.value)

Get the maximum completed order value
orders | max(orders.value) | where(orders.status == 'completed')

Get the maximum order value per country
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

max (AQL Doc)

sum()

Syntax

sum([table], expression)

Description

Calculates the sum of values in the group, excluding NULL values.

Tags

AQL Function, Aggregate Function

Examples

Calculate the sum of order value
orders | sum(orders.value)

Calculate the sum of completed order value
orders | sum(orders.value) | where(orders.status == 'completed')

Calculate the sum of order value per country
orders | group(orders.delivery_country) | select(orders.delivery_country, total_order_value: sum(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

Number: The sum of the values.

Learn more

sum (AQL Doc)

median()

Syntax

median([table], expression)

Description

Computes the median of the values in the group, excluding NULL values.

Tags

AQL Function, Aggregate Function

Examples

Get the median order quantity
median(orders.quantity)

Get the median order quantity (using pipe)
orders | median(orders.quantity)

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 median of the values.

Learn more

median (AQL Doc)

stdev()

Syntax

stdev([table], expression)

Description

Computes the sample standard deviation of the values in the group, excluding NULL values.

Tags

AQL Function, Aggregate Function

Examples

Calculate the sample standard deviation of order value
stdev(orders.value)

Calculate the sample standard deviation of order value (using pipe)
orders | stdev(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

Number: The sample standard deviation.

Learn more

stdev (AQL Doc)

stdevp()

Syntax

stdevp([table], expression)

Description

Computes the population standard deviation of the values in the group, excluding NULL values.

Tags

AQL Function, Aggregate Function

Examples

Calculate the population standard deviation of order value
stdevp(orders.value)

Calculate the population standard deviation of order value (using pipe)
orders | stdevp(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

Number: The population standard deviation.

Learn more

stdevp (AQL Doc)

var()

Syntax

var([table], expression)

Description

Returns the sample variance of the values in the group, excluding NULL values.

Tags

AQL Function, Aggregate Function

Examples

Calculate the sample variance of order value
var(orders.value)

Calculate the sample variance of order value (using pipe)
orders | var(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

Number: The sample variance.

Learn more

var (AQL Doc)

varp()

Syntax

varp([table], expression)

Description

Returns the population variance of the values in the group, excluding NULL values.

Tags

AQL Function, Aggregate Function

Examples

Calculate the population variance of order value
varp(orders.value)

Calculate the population variance of order value (using pipe)
orders | varp(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

Number: The population variance.

Learn more

varp (AQL Doc)

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

AQL Function, Logical Function

Examples

Categorize based on user gender
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

case (AQL Doc)

and()

Syntax

and(condition, ...)

Description

Returns true only when all specified conditions are true.

Tags

AQL Function, Logical Function

Examples

Filter products with IDs >= 2 and <= 8
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

and (AQL Doc)

or()

Syntax

or(condition, ...)

Description

Returns true when at least one of the specified conditions is true.

Tags

AQL Function, Logical Function

Inputs

condition (Truefalse: required, repeatable): The conditions to OR together.

Output

Truefalse: Returns true if any input condition is true, else false.

Examples

Filter products with IDs <= 2 or >= 8
or(products.id <= 2, products.id >= 8)

Learn more

or (AQL Doc)

not()

Syntax

not(condition)

Description

Logical NOT takes a single truefalse expression and returns true when the expression is false.

Tags

AQL Function, Logical Function

Examples

Filter non-null product IDs
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

not (AQL Doc)

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

AQL Function, Time Intelligence Function

Examples

Get timestamp for a specific datetime
epoch(orders.created_at)

Get timestamp for a date
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

AQL Function, Time Intelligence Function

Examples

Truncate a specific datetime dimension
day(orders.created_at)

Using pipe syntax
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

AQL Function, Time Intelligence Function

Examples

Truncate a specific datetime
week(orders.created_at)

Using pipe syntax
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

AQL Function, Time Intelligence Function

Examples

Truncate a specific datetime
month(orders.created_at)

Using pipe syntax
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

AQL Function, Time Intelligence Function

Examples

Truncate a specific datetime
quarter(orders.created_at)

Using pipe syntax
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

AQL Function, Time Intelligence Function

Examples

Truncate a specific datetime
year(orders.created_at)

Using pipe syntax
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

AQL Function, Time Intelligence Function

Examples

Truncate a specific datetime dimension
hour(orders.created_at)

Using pipe syntax
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

AQL Function, Time Intelligence Function

Examples

Truncate a specific datetime dimension
minute(orders.created_at)

Using pipe syntax
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

AQL Function, Time Intelligence Function

Examples

Truncate to day
date_trunc(orders.created_at, 'day')

Truncate to month
date_trunc(orders.created_at, 'month')

Truncate to year
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

AQL Function, Time Intelligence Function

Examples

Extract year
date_part('year', orders.created_at)

Extract quarter
date_part('quarter', orders.created_at)

Extract month
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

AQL Function, Time Intelligence Function

Examples

Extract year number
year_num(orders.created_at)

Using pipe syntax
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

AQL Function, Time Intelligence Function

Examples

Extract quarter number
quarter_num(orders.created_at)

Using pipe syntax
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

AQL Function, Time Intelligence Function

Examples

Extract month number
month_num(orders.created_at)

Using pipe syntax
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

AQL Function, Time Intelligence Function

Examples

Extract week number
week_num(orders.created_at)

Using pipe syntax
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

AQL Function, Time Intelligence Function

Examples

Extract day of week number
dow_num(orders.created_at)

Using pipe syntax
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)

day_num()

Syntax

day_num(datetime)

datetime | day_num()

Description

Extracts the day of month number from a datetime value.

Tags

AQL Function, Time Intelligence Function

Examples

Extract day number
day_num(orders.created_at)

Using pipe syntax
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

AQL Function, Time Intelligence Function

Examples

Extract hour number
hour_num(orders.created_at)

Using pipe syntax
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

AQL Function, Time Intelligence Function

Examples

Extract minute number
minute_num(orders.created_at)

Using pipe syntax
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

AQL Function, Time Intelligence Function

Examples

Extract second number
second_num(orders.created_at)

Using pipe syntax
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

AQL Function, Time Intelligence Function

Examples

Calculate days between created_at and now
date_diff('day', orders.created_at, @now)

Calculate months between created_at and 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

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

AQL Function, Null/Zero Handling Function

Examples

Select the first available payment value
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

coalesce (AQL Doc)

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

AQL Function, Null/Zero Handling Function

Examples

Handle divide-by-zero in sales calculation with nullif
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

nullif (AQL Doc)

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

AQL Function, Null/Zero Handling Function

Examples

Safely calculate average order value
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

safe_divide (AQl Doc)

abs()

Syntax

abs(number)

Description

Returns the absolute value of a number, removing any negative sign and returning the non-negative magnitude.

Tags

AQL Function, Mathematical Function

Examples

Get absolute value of -1 and 5
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

abs (AQL Doc)

sqrt()

Syntax

sqrt(number)

Description

Calculates the square root of a given number.

Tags

AQL Function, Mathematical Function

Examples

Get square root of 9 and 10
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

sqrt (AQL Doc)

ceil()

Syntax

ceil(number)

Description

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

Tags

AQL Function, Mathematical Function

Examples

Get rounding up to the nearest integer of 1.1 and 1.9
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

ceil (AQL Doc)

floor()

Syntax

floor(number)

Description

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

Tags

AQL Function, Mathematical Function

Examples

Get rounding down to the nearest integer of 1.1 and 1.9
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

floor (AQL Doc)

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

AQL Function, Mathematical Function

Examples

Get rounding to the nearest integer
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

round (AQL Doc)

trunc()

Syntax

trunc(number, [scale])

Description

Truncates a number to a specified number of decimal places, removing digits beyond the specified scale.

Tags

AQL Function, Mathematical Function

Examples

Truncate numbers to specified decimal places
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

trunc (AQL Doc)

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

AQL Function, Mathematical Function

Examples

Get exponential function with base e of 1
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

exp (AQL Doc)

ln()

Syntax

ln(number)

Description

Calculates the natural logarithm (base e) of a given number.

Tags

AQL Function, Mathematical Function

Examples

Get natural logarithm function with base e of 1 and e
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

ln (AQL Doc)

log10()

Syntax

log10(number)

Description

Calculates the base 10 logarithm of a given number.

Tags

AQL Function, Mathematical Function

Examples

Get base-10 logarithm of 100 and 10
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

log10 (AQL Doc)

log2()

Syntax

log2(number)

Description

Calculates the base 2 logarithm of a given number.

Tags

AQL Function, Mathematical Function

Examples

Get base-2 logarithm of 8 and 16
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

log2 (AQL Doc)

pow()

Syntax

pow(base, exponent)

Description

Raises a base number to the power of an exponent.

Tags

AQL Function, Mathematical Function

Examples

Calculate 2^3 and 10^2
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

pow (AQL Doc)

mod()

Syntax

mod(dividend, divisor)

Description

Returns the remainder of a division operation.

Tags

AQL Function, Mathematical Function

Examples

Get the remainder of 5/2 and 10/3
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

mod (AQL Doc)

div()

Syntax

div(dividend, divisor)

Description

Returns the integer quotient of a division operation.

Tags

AQL Function, Mathematical Function

Examples

Get the integer quotient of 5/2 and 10/3
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

div (AQL Doc)

sign()

Syntax

sign(number)

Description

Returns the sign of a number: 1 for positive, -1 for negative, and 0 for zero.

Tags

AQL Function, Mathematical Function

Examples

Get sign of 5, -5, and 0
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

sign (AQL Doc)

radians()

Syntax

radians(degrees)

Description

Converts degrees to radians.

Tags

AQL Function, Mathematical Function

Examples

Convert 180 degrees to radians
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

radians (AQL Doc)

degrees()

Syntax

degrees(radians)

Description

Converts radians to degrees.

Tags

AQL Function, Mathematical Function

Examples

Convert pi radians to degrees
degrees(pi())  // Returns 180

Inputs

radians (Number: required): The angle in radians to convert.

Output

Number: The angle converted to degrees.

Learn more

degrees (AQL Doc)

pi()

Syntax

pi()

Description

Returns the mathematical constant π (pi).

Tags

AQL Function, Mathematical Function

Examples

Get pi number
pi()  // Returns approximately 3.14159

Inputs

Output

Number: The value of π.

Learn more

pi (AQL Doc)

acos()

Syntax

acos(number)

Description

Returns the arccosine (inverse cosine) of a number.

Tags

AQL Function, Mathematical Function

Examples

Get inverse cosine of cosine π
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

acos (AQL Doc)

asin()

Syntax

asin(number)

Description

Returns the arcsine (inverse sine) of a number.

Tags

AQL Function, Mathematical Function

Examples

"Get
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

asin (AQL Doc)

atan()

Syntax

atan(number)

Description

Returns the arctangent (inverse tangent) of a number.

Tags

AQL Function, Mathematical Function

Examples

Get inverse tangent of tangent π/4
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

atan (AQL Doc)

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

AQL Function, Mathematical Function

Examples

Get inverse tangent of coordinates (2, π)
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

atan2 (AQL Doc)

cos()

Syntax

cos(number)

Description

Returns the cosine of a number.

Tags

AQL Function, Mathematical Function

Examples

Get cosine of pi
cos(pi())  // Returns -1

Inputs

number (Number: required): The angle in radians.

Output

Number: The cosine of the input number.

Learn more

cos (AQL Doc)

sin()

Syntax

sin(number)

Description

Returns the sine of a number.

Tags

AQL Function, Mathematical Function

Examples

Get sine of pi/2
sin(pi() / 2)  // Returns 1

Inputs

number (Number: required): The angle in radians.

Output

Number: The sine of the input number.

Learn more

sin (AQL Doc)

tan()

Syntax

tan(number)

Description

Returns the tangent of a number.

Tags

AQL Function, Mathematical Function

Examples

Get tan of pi/4
tan(pi() / 4) // => 1

Inputs

number (Number: required): The angle in radians.

Output

Number: The tangent of the input number.

Learn more

tan (AQL Doc)

cot()

Syntax

cot(number)

Description

Returns the cotangent of a number.

Tags

AQL Function, Mathematical Function

Examples

Get cotangent of pi/4
cot(pi() / 4) // => 1

Inputs

number (Number: required): The angle in radians.

Output

Number: The cotangent of the input number.

Learn more

cot (AQL Doc)

cast()

Syntax

cast(expr, type)

Description

Returns the input value casted to the specified data type.

Tags

AQL Function, Miscellaneous Function

Examples

Convert 2000 in string to integer
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

cast (AQL Doc)

concat()

Syntax

concat(text, [text ...])

Description

Returns the concatenated string of multiple strings.

Tags

AQL Function, Miscellaneous Function

Examples

Concatenate strings to Hello World
concat('Hello', ' ', 'World')

Inputs

text (Text: required, repeatable): The strings to concatenate.

Output

Text: The concatenated string.

Learn more

concat (AQL Doc)

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

AQL Function, Miscellaneous Function

Examples

Calculate sales ratio by city or country level of detail
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

Learn more

is_at_level (AQL Doc)

select()​

Syntax​

Description​

Tags​

Examples​

Inputs​

Output​

Learn more​

group()​

Syntax​

Description​

Tags​

Examples​

Inputs​

Output​

Learn more​

filter()​

Syntax​

Description​

Tags​

Examples​

Inputs​

Output​

Learn more​

unique()​

Syntax​

Description​

Tags​

Examples​

Inputs​

Output​

Learn more​

top()​

Syntax​

Description​

Tags​

Examples​

Inputs​

Output​

Learn more​

bottom()​

Syntax​

Description​

Tags​

Examples​

Inputs​

Output​

Learn more​

where()​

Syntax​

Description​

Tags​

Examples​

Inputs​

Output​

Learn more​

with_relationships()​

Syntax​

Description​

Tags​

Examples​

Inputs​

Output​

Learn more​

of_all()​

Syntax​

Description​

Tags​

Examples​

Inputs​

Output​

Aliases​

Learn more​

keep_grains()​

Syntax​

Description​

Tags​

Aliases​

Examples​

Inputs​

select()

Syntax

Description

Tags

Examples

Inputs

Output

Learn more

group()

Syntax

Description

Tags

Examples

Inputs

Output

Learn more

filter()

Syntax

Description

Tags

Examples

Inputs

Output

Learn more

unique()

Syntax

Description

Tags

Examples

Inputs

Output

Learn more

top()

Syntax

Description

Tags

Examples

Inputs

Output

Learn more

bottom()

Syntax

Description

Tags

Examples

Inputs

Output

Learn more

where()

Syntax

Description

Tags

Examples

Inputs

Output

Learn more

with_relationships()

Syntax

Description

Tags

Examples

Inputs

Output

Learn more

of_all()

Syntax

Description

Tags

Examples

Inputs

Output

Aliases

Learn more

keep_grains()

Syntax

Description

Tags

Aliases

Examples

Inputs