Metric Expression Editor for derived metrics

You can create new metrics (called derived metrics) based on attributes and metrics that have already been added to a dashboard or document. For a more detailed description of derived metrics in dashboards, see Creating a metric based on existing objects: Derived metrics. For a more detailed description of derived metrics in documents, see the Document Creation Guide.

You can use the Metric Expression Editor to create a derived metric from scratch, which allows you to customize the metric and make it more sophisticated to fit your needs. Steps to create and edit a metric are provided below.

Use the Metric Expression Editor to:

Build custom expressions, such as percent-to-total metrics or expressions that use custom functions created with the MicroStrategy Function Plug-In Wizard. For an introduction to the MicroStrategy Function Plug-In Wizard, see the Functions Reference.
Create a metric by typing the metric formula directly.
Add conditional calculations, to create groups of data by filtering metric values. For example, you can group regions into poor revenue producers and good revenue producers. This supplements the conditional analysis provided by filters and thresholds. For steps, see Performing conditional calculations: If and Case functions.

For example, you can create a nested metric, where one aggregation function is enclosed inside another. A sample metric formula is Avg(Sum(Revenue)). The inner function Sum calculates the total for the Revenue fact, and the outer function Avg calculates the average of that result. If you want to know the average revenue per category for every region, you can create a nested metric that first calculates the revenue for all categories, and then calculates the average for each region. The metric is defined as:

Avg(Sum(Revenue) {~, Category}) {~, Region}

The inner formula, Sum(Revenue), calculates the revenue at the Category level. The outer formula then calculates the average of the resulting values at the Region level.

To create a metric, you must define the metric's formula, which consists of arithmetic operators (+, -, *, and /) and functions. The operators and functions can be applied to attributes or metrics. An example of the formula of a compound metric is RunningAvg(Cost), where Cost is a metric and RunningAvg is a function that calculates the running average. Another example is Sum(Cost) + Sum(Profit), where Cost and Profit are metrics and Sum is a function that calculates the sum. In this case, the summation of the Cost metric is added to the summation of the Profit metric.

To add functions to the metric formula, you must select any options required to define the function. For example, if you want to add a grouping function, such as Sum, Average, First, or Maximum, you must define the function's expression, and you can also change the attribute level that the metric aggregates at. If you want to add a non-grouping function, such as data mining, date, OLAP, and ranking functions, you must define the input values (called arguments) for the function, as well as any parameters you can use to determine the behavior of the function. For the syntax of metric formulas that use grouping functions, see the Advanced Metrics chapter of the Advanced Reporting Guide. For the syntax for non-grouping functions, see the Functions Reference.

For a dashboard, you can create a derived metric and immediately display it on a visualization, or you can create and add a derived metric to a dashboard without adding it to a specific visualization. Both methods add the derived metric to the list of dataset objects in the dashboard's Datasets panel, so that you can use the metric in visualizations, text fields, and so on.

For a document, you can create a derived metric and immediately display it on a Grid/Graph, or you can create and add a derived metric to a document without adding it to a specific Grid/Graph. Both methods add the derived metric to the list of dataset objects in the document's Dataset Objects pane, so that you can use the metric in Grid/Graphs, data fields, and so on.

Follow the steps below to create sophisticated derived metrics from scratch, using attributes, metrics, functions, and arithmetic operators.

If you want to create a metric by selecting objects and being guided through the process, and the metric that you want to create does not include custom expressions, click Switch to Function Editor, then see To create a basic derived metric.

Prerequisites

If you want to add the derived metric to a visualization in a dashboard, you must have already created the visualization. For steps, see About visualizations.
If you want to add the derived metric to a Grid/Graph in a dashboard, you must have already created the Grid/Graph. For steps, see the Document Creation Guide.
You are comfortable working with functions and metric levels.
You must have the Web Edit Dashboard and Web Run Dashboard privileges.

To create a derived metric from scratch

1 Click the name of the dashboard or document to open it.
2 Do one of the following:
To create a derived metric in a dashboard, click the name of the dashboard  to open it. You can add the derived metric immediately to a specific visualization to be displayed. Do one of the following:
To create the metric without adding it to a visualization, from the Datasets panel, right-click the dataset to add the metric to, and select New Metric.
To create the metric and add it immediately to a specific visualization, from the visualization’s Editor panel, right-click the metric to use to create the derived metric, and select New Metric.

The Metric Formula Editor opens.

To create a derived metric in a document, open the document in Design or Editable Mode. You can add the derived metric immediately to a specific Grid/Graph to be displayed. Do one of the following:
To create a derived metric in a Grid/Graph, right-click a Grid/Graph in the document’s Layout area and select Insert New Metric. The Metric Formula Editor opens.
To create a derived metric based on an attribute in a Grid/Graph, right-click the attribute in the Grid/Graph, point to Insert Metric, and select the function. The derived metric is created, and you can skip the remaining steps of this procedure.
To create a derived metric directly from a metric in the document, from the Dataset Objects pane, right-click the metric on which to base the derived metric, and select Insert New Metric. The Metric Formula Editor opens.

You must be in Design Mode to access the Dataset Objects pane.

To create a derived metric directly from a metric or an attribute in the document and base the derived metric on a function, right-click the metric or attribute in the Layout area and select Insert Function. The Insert Function dialog box opens. Continue creating this derived metric by following the steps to create a summary metric in the Using a function to create a subtotal: Creating summary metrics section in the Document Creation Guide.
3 Type a name for the metric in the Metric Name field.
4 In the editor pane to the right, begin typing the metric formula.
You can type arithmetic operators (such as + and -) and values directly in the metric expression.
You can quickly add objects such as attributes, metrics, and functions to the metric, as described in the steps below.
5 To add a dataset object to the metric, from the Objects list, double-click the name of an object to add it to the editor pane. You can narrow down the list of dataset objects displayed by typing the name of the object in the search field, or by selecting a specific category of dataset object from the drop-down list, as follows:
To display all dataset objects across all datasets in the dashboard or document, select All.
To display the dataset objects in a specific dataset, select the name of the dataset.
To display all dataset objects that are not metrics, such as attributes, custom groups, and consolidations, select Attribute.
To display metrics only, select Metric.
6 To add a function to the metric, perform the following steps:
a From the Functions list to the left, browse to and select the function to use to create the derived metric.  You can filter the list of functions displayed by typing the name of a function in the search field, or selecting a function category from the drop-down list. You can click Details at the bottom of the dialog box to see a description of the function, examples, and syntax information.
b Hover the cursor over the name of the function, then click Edit.
c If you selected an aggregation function (also known as a grouping function), such as Sum, Average, First, or Maximum, the Expression field is displayed, along with settings for defining the level. Perform the following steps:
a Type the function expression in the Expression field. As you type, matching objects are displayed in a drop-down list. You can click an object or continue to type. You can type multiple objects, such as Revenue-Profit.
b If the Function Parameters icon Function Parameters dialog box is displayed, you can define function parameters, such as whether to use all values in the calculation or to calculate using only the unique values. Click the Function Parameters icon Function Parameters dialog box. Select the appropriate options to define each parameter, then click OK to apply your changes. For a list of the function parameters for a specific function, click Details at the bottom of the dialog box.
c By default, the function is calculated at the level of the attributes on the visualization, Grid/Graph, or document section on which the metric is placed. You can specify the attributes to use in the metric calculation, regardless of where the metric is placed. To change the level, select an attribute from the Level drop-down list. You can add multiple attributes.
d If you selected a non-grouping function, such as data mining, date, OLAP, and ranking functions, you are presented with options to define the input values (called arguments) for the function, as well as any parameters you can use to determine the behavior of the function. To view a list of the arguments and parameters for the function, click Details at the bottom of the dialog box.

For example, the NTile function has two parameters, Ascending and Tiles. Ascending controls whether the NTiles are ordered in ascending or descending order, while Tiles sets the number of splits.

Perform the following steps:

a For each argument listed, type a value to use as input values of the function.
b For each parameter listed, type a value or select the parameter value from the drop-down list.
e When finished, click Add to add the function to the metric. The function, including the arguments you provided, is displayed in the editor pane.
7 Repeat the appropriate steps above as desired to define your metric formula. For example, you can add another metric, operator, or function to the formula. To delete the metric formula and begin again, click Clear All.
8 When you have completed the metric formula, click Validate. The Expression Editor displays a status message below the metric expression, indicating whether the new metric expression is correct. When the message displays as Valid Metric Formula, the metric expression is correct.
9 By default, the aggregation and subtotal behavior is automatically determined. You can change the behavior, to specify whether the derived metric is calculated using the whole dataset or calculated using the data in the visualization or document section that it is placed on. For an explanation of when you need to change the behavior, and steps to do so, see Changing the aggregation and subtotal behavior for a derived metric.
10 When finished, click Save to save your changes. Your derived metric is created and added to the dashboard or document.

Performing conditional calculations: If and Case functions

Conditional calculations can be used to supplement the conditional analysis provided with filtering and thresholds. For example, you can provide conditional analysis by combining data into different groups based on the value of one or more metrics in a dashboard or document.

For example, Visual Insight comes with the Airline Flight Analysis sample dashboard, which includes the following Day of the Week Stats visualization:

Day of the Week Stats visualization

You can assess the performance of flights based on the average on-time percentage. For example, an average on-time percentage of 60% or lower is considered poor performance, while anything else is considered good performance. You can create a new Performance metric that includes a conditional analysis based on the Avg On-time (%) metric, as shown below.

Day of the Week Stats visualization with conditional Performance metric

This conditional analysis can be done by creating a derived metric that uses the If conditional function. The Performance metric shown above uses the following formula:

IF(([Avg On-time (%)] <= .6),"Poor","Good")

You can then continue this analysis by selecting the cell that says Good in the row for Sunday, then from the drop-down list point to Drill, and select Origin Airport Name. This updates the visualization to include the origin airport and display data for Sunday only, as shown below.

Drilled visualization

If you want to qualify the values of a metric into more than two groups, you can create a derived metric using the Case function. For example, you can modify the Performance metric to use the following expression, which qualifies performance into three groups rather than only two groups:

Case(([Avg On-time (%)] <= 0.6),"Poor",([Avg On-time (%)] > 0.65),"Excellent","Good")

This expression will display the results in the example below, which includes an Excellent group to denote airports that have an average on-time percentage greater than 65%.

Derived metric with Case function

The Case function provides the same conditional analysis as the If function. However, the If function can only qualify metric values into two different groups, while the Case function can qualify metric values into two or more groups. The Case function allows you to specify two or more conditions to use to evaluate the metrics you are interested in.

You can apply qualifications on more than one metric in an If or Case function. For example, you can assess the performance of flights based on both the average on-time percentage and the average delay time in minutes. Having an average on-time percentage of 60% or lower combined with an average delay time of 15 minutes or greater is considered poor performance, while anything else is considered good performance. You can create a new Performance metric that includes a conditional analysis based on the Avg On-time (%) metric and the Avg Delay (min) metric, as follows:

IF((([Avg On-time (%)] <= 0.6) And ([Avg Delay (min)] >= 15)),"Poor","Good")

The example below shows the results of this conditional analysis.

Assessing flight performance on 2 metrics

Since a conditional analysis is created as a derived metric, rather than included in a visualization as a filter, all the results of a conditional analysis are displayed on a visualization at once. In the examples above, this allows you to view data about all airports, both those that are performing well and those that are performing poorly. Applying a filter to a visualization, in contrast, hides the data on a visualization that does not meet the filter condition.

Prerequisite

You must have the Web Edit Dashboard and Web Run Dashboard privileges.

To perform conditional calculations with derived metrics

1 Click the name of the dashboard or document to open it.
2 From the Datasets or Dataset Objects panel, right-click the dataset to add the metric to, and select New Metric. The Metric Formula Editor opens.
If the Datasets panel is not displayed, from the View menu, select Datasets Panel.
3 Type a name for the metric in the Metric Name field.
4 From the Functions list to the left, browse to and select the If or Case function to use to create the derived metric.
5 Hover the cursor over the name of the function, then click the pencil icon. The options to define the input values (called arguments) for the function are displayed. To view details of the arguments for the function, click Details at the bottom of the dialog box.
6 Type the values to use as arguments, as described below:
The syntax for the If function is IF(Condition, TrueResult, FalseResult) where:
Condition is the conditional expression. The condition can contain metrics, comparison and logical operators, and constants. The condition must be evaluated to be TRUE or FALSE.
TrueResult is a constant or metric value to return if the condition is TRUE. To display text as the value that is returned, enclose the text in double quotes (" ").
FalseResult is a constant or metric value to return if the condition is FALSE. To display text as the value that is returned, enclose the text in double quotes (" ").
The syntax for the Case function is Condition(Condition1, ReturnValue1, Condition2, ReturnValue2,..., DefaultValue) where:
Condition1 is the first condition to evaluate. The condition can contain metrics, comparison and logical operators, and constants.
ReturnValue1 is a constant or metric value to return if the Condition1 condition is TRUE. To display text as the value that is returned, enclose the text in double quotes (" ").
Condition2 is the second condition to evaluate. The condition can contain metrics, comparison and logical operators, and constants.
ReturnValue2 is a constant or metric value to return if the Condition2 condition is TRUE. To display text as the value that is returned, enclose the text in double quotes (" ").
The ... (ellipsis) represents any number of condition and return value combinations that can be used to create another logical group. Every condition must have a return value.
DefaultValue is a constant or metric value to return if none of the conditions are TRUE. To display text as the value that is returned, enclose the text in double quotes (" ").
7 When finished, click Add to add the function to the metric. The function, including the arguments you provided, is displayed in the editor pane.
8 Click Validate. The Formula Editor displays a status message below the metric expression, indicating whether the new metric expression is correct. When the message displays as Valid Metric Formula, the metric expression is correct.
9 When finished, click Save to save your changes. Your derived metric is created and added to the dashboard. You can now add the derived metric to a visualization, as described in About visualizations.

Related topics

Metric Function Editor for derived metrics in dashboards
Editing or deleting derived metrics in a dashboard
Creating a filter for the data on a visualization
Thresholds Editor for dashboards