Source a Metric from another Metric to Create a Composite/Formula Metric
Data availability is the key to identifying the appropriate source for your new Metric. When sourcing Metric data from other existing elements it is essential to decide which type of element to use (either Reports OR Metrics) since both element types cannot be used as Data sources for a single Metric.
The syntax required to construct the 'Metric Formula' sourced from other Metrics is simpler than the command required to use data from one or more Reports since the fetch command requires only using the source Metric’s <element_id> preceded by a colon “:”. This feature also gives you the ability to define a Composite (Formula) Metric to create a calculated metric by combining two or more Metrics arithmetically. You can compute the value of this new Metric using the Element ID's and a mathematical symbol rather than having to construct a SQL Statement.
In the example covered in this article we describe how we can use data from 'Daily Sales' and 'Daily Units Sold' Metrics to calculate data for 'Total Daily Sales per Unit' Metric.
To obtain data from a Report instead of a Metric, see Source a Metric from an Existing Report
2. Complete Data tab information
- Select 'Existing Metrics' as a Data Source
- If you do not know the Element ID of the Metric that has the data you need for this new Metric, click Find Source Metrics
- The Add Source Metric pop-up opens
- Use the drop-down list to choose 'All Categories' or select a Category to narrow the choice of Elements
- Choose a Metric
Click Add to Formula.
3. Element (Metric) ID in the 'Metric formula'
Notice that the Element ID of the first selected Metric is placed in the Metric Formula text box and is preceded by a colon (:). Additionally, each time you enter or select a source Metric, its Element ID and Name appear in the hint's text box as shown below above.
Repeat the processes in the Step 2 to select another Metric or enter a Metric's Element ID and the mathematical operation symbol of the calculation that is required.
3.1. Alternatively, find <element ID> of a source Metric in its URL
When element Viewer or Editor is opened Element ID is always specified its URL as the number preceded by the word 'element'.
4. Finalize the formula
- After you add a second source Metric's Element ID, complete your formula by defining the method of calculating your new metric.
- Click Validate Formula
In the example above, notice that the Element ID's of two elements construct a formula that divides the value from Element 1 ('Daily Sales' Metric) by the corresponding value from Element 5 ('Daily Units Sold' Metric).
- You can combine more than two source Metrics if you like.
The IFNULL function provides a way to gracefully handle missing Metric values. For instance, the formula above could be modified to read " :1 / IFNULL( :5, 1 ) ". If the value for Metric #5 is missing for any particular calculation, Metric Insights will insert a value of '1' in its place.
6. Review and generate your Metric's Chart
- Last collected for date is displayed
- Enable & publish to generate your Metric's Chart