Skip to main content

Metric Functions Overview

Metric functions transform an input metric expression into another metric expression, usually through modifying the context of that input expression. These functions are broken down into these categories:

Condition Functions

Condition Functions restrict the values to be aggregated for a metric based on specified conditions.

FunctionSyntaxPurpose
wherewhere(measure, condition)Apply condition(s) to original measure

Relationship Functions

Relationship Functions change the default relationships defined at dataset level for a specific metric.

FunctionSyntaxPurpose
with_relationshipswith_relationships(measure, relationship)Specifies what existing relationship(s) to used for this measure calculation

LOD Functions

LOD Functions modify the Level of Detail of a metric to serve specific analytics needs.

FunctionSyntaxPurpose
evaleval(measure, metric_function)Apply metric function(s) to modify the original measure
exclude, exclude_grains, of_allof_all(expression, model, dim)Exclude dimension(s) from a measure calculation
exact_grainsexact_grains(measure, model, dim)Choose exact dimension(s) for this measure calculation

Time-based Functions

Time-based Functions are functions that change the time context of a metric to serve analytics needs like cumulative total, period-to-date, comparison with previous periods, etc.

FunctionSyntaxPurpose
running_totalrunning_total(measure, running_dimension)Calculates a running total measure
period_to_dateperiod_to_date(measure, date_part, date_dimension)Calculates a cumulative period to date measure
exact_periodexact_period(measure, time_dimension, time_range) Calculates a measure in a custom period
relative_periodrelative_period(measure, time_dimension, interval)Calculates a measure with in a relative time period
trailing_periodtrailing_period(measure, date_dimension, periods)Calculates a measure in trailing periods

Window Functions

AQL Window Functions provide ways to do secondary calculations like navigating (previous, next), ranking, or aggregating (sum, avg, etc.) across a subset of rows of a table. These are analogous to SQL window functions. For detailed information about how Window Functions work in general, see Window Functions Overview.

FunctionSyntaxPurpose
rankrank(order, partition)Calculates the (skip) rank of a value
dense_rankdense_rank(order, partition)Calculates the dense rank of a value
previousprevious(value, order_by)Calculates the value in the previous row
nextnext(value, order_by)Calculates the value in the next row
window_sumwindow_sum(measure, order_by, partition)Calculates the sum of measure values
window_avgwindow_avg(measure, order_by, partition)Calculates the average of measure values
window_minwindow_min(measure, order_by, partition)Calculates the minimum of measure values
window_maxwindow_max(measure, order_by, partition)Calculates the maximum of measure values
window_countwindow_count(measure, order_by, partition)Calculates the count of measure values
window_stdevwindow_stdev(measure, order_by, partition)Calculates the standard deviation of measure values
window_stdevpwindow_stdevp(measure, order_by, partition)Calculates the population standard deviation of measure values
window_varwindow_var(measure, order_by, partition)Calculates the variance of measure values
window_varpwindow_varp(measure, order_by, partition)Calculates the population variance of measure values

Let us know what you think about this document :)