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.
| Function | Syntax | Purpose |
|---|---|---|
| where | where(measure, condition) | Apply condition(s) to original measure |
Relationship Functions
Relationship Functions change the default relationships defined at dataset level for a specific metric.
| Function | Syntax | Purpose |
|---|---|---|
| with_relationships | with_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.
| Function | Syntax | Purpose |
|---|---|---|
| eval | eval(measure, metric_function) | Apply metric function(s) to modify the original measure |
| exclude, exclude_grains, of_all | of_all(expression, model, dim) | Exclude dimension(s) from a measure calculation |
| keep, keep_grains | keep_grains(measure, model, dim) | Keep only dimension(s) for this measure calculation |
| dimensionalize | dimensionalize(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.
| Function | Syntax | Purpose |
|---|---|---|
| running_total | running_total(measure, running_dimension) | Calculates a running total measure |
| period_to_date | period_to_date(measure, date_part, date_dimension) | Calculates a cumulative period to date measure |
| exact_period | exact_period(measure, time_dimension, time_range) | Calculates a measure in a custom period |
| relative_period | relative_period(measure, time_dimension, interval) | Calculates a measure with in a relative time period |
| trailing_period | trailing_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.
| Function | Syntax | Purpose |
|---|---|---|
| rank | rank(order, partition) | Calculates the (skip) rank of a value |
| dense_rank | dense_rank(order, partition) | Calculates the dense rank of a value |
| previous | previous(value, order_by) | Calculates the value in the previous row |
| next | next(value, order_by) | Calculates the value in the next row |
| window_sum | window_sum(measure, order_by, partition) | Calculates the sum of measure values |
| window_avg | window_avg(measure, order_by, partition) | Calculates the average of measure values |
| window_min | window_min(measure, order_by, partition) | Calculates the minimum of measure values |
| window_max | window_max(measure, order_by, partition) | Calculates the maximum of measure values |
| window_count | window_count(measure, order_by, partition) | Calculates the count of measure values |
| window_stdev | window_stdev(measure, order_by, partition) | Calculates the standard deviation of measure values |
| window_stdevp | window_stdevp(measure, order_by, partition) | Calculates the population standard deviation of measure values |
| window_var | window_var(measure, order_by, partition) | Calculates the variance of measure values |
| window_varp | window_varp(measure, order_by, partition) | Calculates the population variance of measure values |