Data availability is the key to identifying the appropriate source for your new Metric. There is not necessarily a "best" approach if the data you want is in both a metric and a report. Ultimately, you must decide which type of element to use to source your 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 your this new Metric using the Element ID's and a mathematical symbol rather than having to construct a SQL Statement.
Determine which Metrics you want to use for your Metric's data
To obtain data from a Report instead of a Metric, see Source a Metric from an Existing Report
1. Specify your Metric Calculations
- 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 link
- 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
1.1. Continue Formula Construction
- Notice that the Element ID of the first selected Metric is placed in the Metric Formula text box and is preceded by a colon (:)
- Each time you enter or select a source Metric, its Element ID and Name appear in the hint's text box as shown below:
Repeat the processes in the previous Step to select another Metric or enter a Metric's Element ID and the mathematical operation symbol of the calculation that is required
Additional Information about how the Metric Formula is populated:
In order to simplify your writing of a formula, the following cursor behavior follows your use of the "Find Source Metrics" link:
- After selecting first Metric selection, a reference for the newly added metric (e.g., :1) is automatically placed into the fetch command box followed by a space (' '), and the cursor is placed in the text box immediately following the space.
- If your cursor was in the fetch command box when you left the Editor, the cursor position in the field is remembered. After you have selected a new Metric, it is placed into the fetch command at the last location of the cursor followed by a space (' '). Focus is returned to the fetch command box, and the cursor is placed immediately after the added space.
1.2. Finalize the formula
After you add a second source Metric, complete your formula by defining the method of calculating your new metric;. The default calculation is adding the two sources together.
- In the example above, notice that the Element ID's of two elements are used to construct a formula that divides the value from Element 1(Daily Sales) divided by the corresponding value from Element 5 (Daily Units Sold) value for theMeasurement Date
Click Validate Formula
- You can combine more than two source Metrics if you like. You are not limited to only two.
- 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.
1.3. Confirm the calculation
Click OK to close the pop-up
1.4. Provide Substitution Parameters
- Change the default values if desired
2. Review the data collection results
- Review the validation results and sample calculation based on the Substitution Parameters selected on the previous pop-up
Click Collect Data to populate your Metric
3. Review fetch results and generate your Metric's chart
- Review the number of Metric values fetched
When you have completed defining your Metric, click Enable & Publish to generate your Metric's chart
4. Open the Editor of one of the Metrics included in the formula
Since, in this example, your 'Metric Formula' was based upon two other Metrics, open the Editor of either of these Metrics (in the sample above, Element 5's Editor is open
- Page down to the Included in Metric Formula section/ on the Metric's Editor, located just below the 'Drill-To Elements' section: NOTE: This section/grid is ONLY visible if the Metric is included in one or more Composite (Formula) Metrics. This allows you see which composite elements are sourced from this Metric. This is helpful if you modify the settings of source element; e.g., change the fetch command, you may need to identify all of the composite metrics that may be affected and make required changes.
- The grid contains the Metric's ‘Element ID ‘and the newly created Composite Metric's 'Name' as a hyperlink