Advanced Metrics Formula Functions
An Advanced Metric is a Formula that performs some action on one or more fields in your Dataset.
A Formula can perform arithmetic and math (future improvement can include text, date and geodata manipulation).
The output of a Formula can be used in as metric in Charts or KPIs.
Data type
The data type of an Advanced Metric Formula is a Number.
Formulas can use Arithmetic or aggregation functions to create a Number type field.
Parentheses can be used to force calculation order.
Aggregation
Formulas for Advanced Metrics requires Aggregation functions to summarize data in one value.
One Field Aggregation (Simple Metric)
SUM(Profit)
Multiple Field Aggregation with Math Operation
SUM(Profit) / SUM(Revenue)
Multiple Field Aggregation with Math Operation and Constant
SUM(Profit) / SUM(Revenue) * 100
Aggregation Functions List
First Implementation
| Type | Name | Description | Syntax | Input Data Type | Output Data Type |
|---|---|---|---|---|---|
| Aggregation | SUM | Return the sum of all values of X. | SUM(X) | List of NUMBER | NUMBER |
| Aggregation | COUNT | Return the number of values of X. | COUNT(X) | List of NUMBER, STRING | NUMBER |
| Aggregation | AVG | Return the average of all values of X. | AVG(X) | List of NUMBER | NUMBER |
| Aggregation | MIN | Return the minimum value of X.2 | MIN(X) | List of NUMBER | NUMBER |
| Aggregation | MAX | Return the maximum value of X. | MAX(X) | List of NUMBER | NUMBER |
| Aggregation | COUNT_DISTINCT | Return the number of unique values of X. | COUNT_DISTINCT(X) | List of NUMBER, STRING | NUMBER |
| Aggregation | VARIANCE | Return the variance of X. | VARIANCE(X) | List of NUMBER | NUMBER |
| Aggregation | STDEV | Return the standard deviation of X. | STDEV(X) | List of NUMBER | NUMBER |
Next Implementation
| Type | Name | Description | Syntax | Input Data Type | Output Data Type |
|---|
Future Implementation
| Type | Name | Description | Syntax | Input Data Type | Output Data Type |
|---|---|---|---|---|---|
| Aggregation | MEDIAN | Return the median of X. | MEDIAN(X) | List of NUMBER | NUMBER |
| Aggregation | PERCENTILE | Return the percentile rank N of field X. | PERCENTILE(X,N) | List of NUMBER | NUMBER |
Math Operations
- Addition:
+ - Substraction:
+ - Division:
/ - Multiplication:
*
Arithmetical Operationis
- POW(value, power)
Math operation can be used to combine together aggregated functions. Use parentheses to force calculation order.
(SUM(price) * SUM(quantity)) * 0.085Standard Scenarios
One Field Aggregation (Simple Metric)
SUM(profit)Multiple Field Aggregation with Operation
SUM(profit) / SUM(revenue)Multiple Field Aggregation with Math Operation and Constant
(SUM(profit) / SUM(revenue)) * 100Complex Multiple Field Aggregation with Math Operation
(SUM(profit) / SUM(revenue)) / COUNT_DISTINCT(item_code)The formula should be resolved following the PEMDAS (or BODMAS) rule.
Wrong Scenarios
Use a field name without an aggregation function
SUM(profit) - revenueReason. A single field cannot be identified considering that input data are always a list of values (rows).
Define a formula without Aggregation Functions
profit - revenueReason. A formula for an Advanced Metric should always aggregate data basing is input in a list of values (rows).
Edge Scenarios
Execute Math Operation inside Aggregation function
SUM(profit - revenue)This could be an edge scenario becouse require a different approach:
- first the Math Operation should be resolved row by row.
- then result can be aggregated with the defined Aggregation function.
Warning: row by row operation could impact performances.