Contents
Introduction
Derived metrics are new metrics that are calculated from existing data. These metrics are not directly collected but are instead computed based on mathematical operations, combinations, or transformations of the original data.
Add a new derived metric
First, navigate to Data Explorer:
Click on Data
Click Data explorer
Next, add a new derived metric:
Select the dataset to which you want to add a derived metric
Click on the context (···) menu
Click Add derived metric
A form will open from the left, containing the following fields:
Name | The name of the derived metric, in snake_case. Once saved, this name cannot be changed, but in most cases the display name is used throughout the system. |
Display name | A display name, in snake_case. This is the name you will see in the interfaces such as the dashboard. |
Description | A text description of the derived metric, that will appear in the data dictionary and drop-down menus. |
Tag | This controls whether or not the pink 'derived' tag appears in drop downs. |
Physical type | How the database stores the value that is created. See Types. |
Logical type | How the interface should interpret the value that is created. See Types. |
Derived expression | An expression using DxQL that determines the output of the derived metric. See below. |
Advanced | Takes a blob of JSON, and should only be used in specific cases. See for instance Hiding Aggregate Functions below. |
After filling out these fields, click Save to create the derived metric.
Edit and existing derived metric
Once created, a derived metric can be edited as follows:
From within Data Explorer, expand the dataset to which the derived metric belongs
Double click the derived metric
OR
Click on the context context (···) menu and click EditOnce you have made your changes, click Save.
Using DxQL to define a derived metric
Derived expressions are how derived metrics are calculated. They are written in DxQL syntax, which is similar to SQL syntax that you may write when creating part of a SELECT statement. They typically take one of two forms:
Mathematical expression
Standard mathematical functions * ,/, +, - and brackets (, ) can be used to calculate a new number based on other columns in the dataset. For instance, lets say I have a column name called item_quantity I can create a derived metric as follows:
(item_quantity + 3) * 100 / 3
For each row in the dataset the derived metric will show the item quantity plus 3, multiplied by 100 divided by 3.
You can also use aggregate functions within a derived metric, so the following could be used:
(SUM(attrition_quantity))/(SUM(issued_quantity))
Note here that because some aggregation happens in the derived metric, you should hide any aggregation in the toolbox. See Hiding aggregate functions.
Case statement
A case statement can be used for conditionally populating a value in the derived column. Here is an example of a case statement with a numerical comparison:
(CASE WHEN line_item_price > 100 THEN 'High Value' WHEN line_item_price < 10 THEN 'Bargain' ELSE 'Regular' END)
We can also use string lookups to create a case statement:
(CASE WHEN product LIKE '%admission%' THEN 'General admission' ELSE 'Other' END)
We can add numbers to a case statement if we want to the aggregate the results in a visualisation:
(CASE WHEN product LIKE '%admission%' THEN 1 ELSE 0 END)
Testing a derived metric
To test the data, you can quickly use a query in the data explorer to review the derived column.
A simple query would be:
SELECT your_derived_metric FROM example_dataset
(In the example above, replace your_derived_metric and example dataset with the appropriate names.)
Note that currently SELECT * does not return all derived columns; derived columns must be named explicitly.
Using AI powered Query Assist 🪄 to create a derived metric
Our AI powered Query Assist is designed to help any user quickly and easily write a valid DxQL filter query.
By describing a derived metric in natural language (i.e. plain English) Query Assist will return a valid DxQL derived expression that best matches the request.
Query Assist uses a Large Language Model (LLM) to interperet the provided query, and other contextual information to make a best guess at the relevant DxQL query.
To use query assist, describe your filter, then click the wand button:
For instance:
multiply quantity by ten
Will return:
line_item_quantity * 10
Note how the assistant is aware of the column names available and will suggest the most appropriate one.
We can also describe case statements in simple English (or indeed many other languages):
when stars are greater than or equal to 4 then great, between 2 and 4 average, and less than 2 bad
Will return:
(CASE WHEN rating >= 4 THEN 'Great' WHEN rating BETWEEN 2 AND 3 THEN 'Average' ELSE 'Bad' END)
The assistant is also aware of many common examples of derived metrics, and so asking for a particular derived metric:
attrition rate
Will return something similar to:
(SUM(issued_quantity) - SUM(redeemed_quantity)) / SUM(issued_quantity)
These examples depends entirely on your dataset.
It is worth checking the query it provides is accurate, and testing the result as explained above is highly advised.
Hiding aggregate functions
If your derived metric includes functions like SUM or AVG then it may not make sense to allow users to then aggregate the results again in the dashboard. In some cases this may cause errors.
In order to hide the aggregate functions for your derived metric you can paste the following into the advanced section of the derived metric form:
{"invalidAggregationFunctions":["COUNT", "SUM", "AVG"]}The above will hide all three aggreagates.