Available calculations
GUI calculations
Below are the supported calculation builders for creating metrics. When none fit, use a Custom formula with AQL.
| Calculation | Description | Use cases | Preview |
|---|---|---|---|
| Count | Counts the total number of items in a group. | Number of orders | |
| Count rows | Counts the number of rows in a table. | Number of records in a model | |
| Count distinct | Counts the total number of distinct items in a group. | Number of unique customers | |
| Average | Calculates the average of values in a group. | Average order value | |
| Min | Returns the item in the group with the smallest value. | Earliest signup date | |
| Max | Returns the item in the group with the largest value. | Highest order amount | |
| Sum | Calculates the sum of values in the group. | Total revenue | |
| Median | Computes the median of the values in the group. | Median order value | |
| Continuous percentile | Returns the value at the given percentile of the sorted expression values, interpolating between adjacent values if needed. | 95th percentile of response time | |
| Discrete percentile | Returns the value at the given percentile of the sorted expression values. If the percentile falls between two values, one of them will be returned (the logic to select the value is database dependent). | 90th percentile of order value | |
| Sample standard deviation | Computes the sample standard deviation of the values in the group. | Variability of order value (sample) | |
| Population standard deviation | Computes the population standard deviation of the values in the group. | Variability of order value (population) | |
| Sample variance | Returns the sample variance of the values in the group. | Sample order-value variance | |
| Population variance | Returns the population variance of the values in the group. | Population order-value variance | |
| List | List values from a data model. | List of products per order | |
| Add (+) | Adds the values of two fields together. | Total cost = price + shipping | |
| Subtract (-) | Subtracts the value of one field from another. | Profit = revenue - cost | |
| Multiply (×) | Multiplies the values of two fields. | Line total = price × quantity | |
| Divide (÷) | Divides the value of one field by another. | Conversion rate = orders / visits | |
| Rank | Assigns a rank to each record based on the order of specified fields, optionally within partitions. | Rank products by total sales | |
| Running total | Calculates a running sum, average, min, or max of a metric along date dimensions. | Cumulative total users | |
| Moving calculations | Calculate a moving calculation on a metric. | 7-day moving average of orders | |
| Count if | Counts the total number of items in a group with conditions. | Number of female users above 30 | |
| Count distinct if | Counts the total number of distinct items in a group with conditions. | Unique customers from a region | |
| Average if | Calculates the average of values in a group with conditions. | Average order value for repeat buyers | |
| Min if | Returns the item in the group with the smallest value with conditions. | First order date for VIP customers | |
| Max if | Returns the item in the group with the largest value with conditions. | Largest order from a campaign | |
| Sum if | Calculates the sum of values in the group with conditions. | Revenue from a specific product line | |
| Case when | Returns the value associated with the first condition that evaluates to true. | Bucket customers into tiers | |
| Percent of total | Calculates the percentage of a metric relative to the original value of that metric. | Revenue share by category | |
| Period over period | Calculates the change of a metric between two periods. | This year's orders vs last year's | |
| Concat | Returns the concatenated string of multiple strings. | Full name from first and last name | |
Formulas
When the available calculations aren't enough, use Custom formula to write your own expression in AQL. This is the same as switching to Formula mode, and it gives you full control over the field's logic.
See full list: AQL functions.