Configure custom metrics with expressions
Besides dragging fields from the right and choosing an aggregation method, you can also add custom metrics directly.
While you're editing a dashboard, custom metric results can't be displayed. You can view them in preview. For more about preview, see Preview, save drafts, and publish
Depending on your analysis scenario, you can generate field expressions with the aggregate functions supported by the current language (such as Trino). The following lists common functions, using Trino as an example. Make sure the logic is correct before you publish, because an invalid expression causes query errors.
Note that the final result of a field expression must be numeric. Custom metrics with non-numeric results are displayed as -.
Basic aggregate functions
You can use the same aggregation methods as for regular metrics
| Function | Description |
|---|---|
| count(x) | Count; null values are excluded |
| count(distinct x) | Count distinct; null values are excluded |
| avg(x) | Average; numeric fields only |
| sum(x) | Sum; numeric fields only |
| max(x) | Maximum; numeric fields only |
| min(x) | Minimum; numeric fields only |
In addition to the aggregate functions above, you can also use the following aggregate functions
| Function | Description |
|---|---|
| count(*) | Count; unlike count(x), empty rows are also counted |
| max_by(x,y) | The value of x associated with the maximum value of y in the input |
| min_by(x,y) | The value of x associated with the minimum value of y in the input |
| approx_distinct(x) | Approximate distinct count, with a 4‰ error compared with count(distinct x) |
For more information, see Aggregate functions
Filtering during aggregation
When using aggregate functions, you can combine FILTER with conditions expressed in a WHERE clause to remove rows from aggregation
Suppose the data is as follows
| channel | pay_amount |
|---|---|
| iOS | 648 |
| Android | 68 |
You can create two custom metrics to convert rows to columns
-- Revenue from the iOS channel
sum("pay_amount") filter(where "channel" = 'iOS')
-- Revenue from the Android channel
sum("pay_amount") filter(where "channel" = 'Android')
Arithmetic operations
You can use arithmetic operators and specific numbers in field expressions to generate formula-based metrics for your business scenarios, such as payment rate and ARPU
| Operator | Description |
|---|---|
| + | Addition |
| - | Subtraction |
| * | Multiplication |
| / | Division |
Note that in Trino, if your field type is int or bigint and you want division results to keep decimals, first use cast(field as double) in the expression before calculating. If any term in an arithmetic operation is null, the final result is also null, so handle null values in advance
Besides the operators above, you may also use the following functions
| Function | Description |
|---|---|
| abs(x) | Absolute value |
| ceiling(x) | Returns x rounded up to the nearest integer |
| floor(x) | Returns x rounded down to the nearest integer |
| approx_distinct(x) | Approximate distinct count, with an error of about 4‰ compared with count(distinct x) |
For more information, see Mathematical functions and operators
Conditional expressions
In custom metrics, you can use conditional expressions to get the metric results you need
| Function | Description |
|---|---|
CASE expression WHEN value THEN result [ WHEN ... ] [ ELSE result ] END | Evaluates conditions from top to bottom and returns the result of the first condition that's met. If none are met, returns the else result |
| if(condition, true_value, false_value) | Evaluates a condition and returns different results depending on whether it's true or false |
| coalesce(value1, value2[, ...]) | Returns the first non-null value. You can use it to avoid null values |
For more information, see Conditional expressions
Window functions
Syntax: function OVER (PARTITION BY partition field ORDER BY sort field)
Note: When you use window functions, the partition fields or sort fields involved must be aggregate functions or fields used in group by (must be an aggregate expression or appear in GROUP BY clause)
In dashboards, group by is performed on the dimensions you select, and the dimensions are aliased in order as group_0, group_1, and so on. Use these names as the partition fields or sort fields
For more information, see Window functions
Aggregate functions
Any aggregate function can be used as a window function by adding an OVER clause
Suppose the data is as follows
| user | channel | pay_amount |
|---|---|---|
| a | iOS | 648 |
| b | Android | 68 |
| c | Android | 6 |
| d | Android | 128 |
The result you want is as follows
| user | channel | Share of revenue within channel |
|---|---|---|
| a | iOS | 648 |
| b | Android | 68 / (68 + 6 + 128) |
| c | Android | 6 / (68 + 6 + 128) |
| d | Android | 128 / (68 + 6 + 128) |
You can drag user and channel into dimensions and create a custom metric
sum("pay_amount")/ sum("pay_amount") over(partition by "group_1")
Ranking functions
| Function | Description |
|---|---|
| rank() | Returns the rank of a value within a group of values. The rank is one plus the number of rows preceding the row that aren't peers of the row |
| dense_rank() | Returns the rank of a value within a group of values |
| row_number() | Returns a unique, sequential number for each row |
Value functions
| Function | Description |
|---|---|
| first_value(x) | Returns the first value of the window |
| last_value(x) | Returns the last value of the window |
| nth_value(x, offset) | Returns the value at the specified offset from the beginning of the window. offset starts at 1 and can be any scalar expression. If it's null or greater than the window, null is returned |
| lead(x[, offset[, default_value]]) | Returns the value at the offset-th row after the current row in the window. The offset can be any scalar expression and defaults to 1 (the next row). If it's null, an error is returned. If it's greater than the window, default_value is returned, or null if default_value isn't specified |
| lag(x[, offset[, default_value]]) | Returns the value at the offset-th row before the current row in the window. The offset can be any scalar expression and defaults to 1 (the previous row). If it's null, an error is returned. If it's greater than the window, default_value is returned, or null if default_value isn't specified |
FAQ
Q: Why isn't the data for my custom metric displayed?
A: Check whether the expression is configured correctly, including:
- Fields must follow the syntax rules. For SR syntax, wrap fields in ``; for Trino syntax, wrap them in ""
- The metric result generated by the expression must be numeric. Non-numeric results from functions such as max(text field) can't be displayed
Q: Why does the query return an error?
A: Check whether the expression is configured correctly, including:
- A field that doesn't exist is used
- The expression is incomplete, for example, a parenthesis is missing
- An unsupported function is used, or a field is used directly without an aggregate function
Q: Can dimension fields be customized?
A: Not currently. You can modify the SQL statement of the sheet to add new dimension fields