MicroStrategy ONE

Add Conditional Calculations to a Derived Metric Using the If and Case Functions

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

Your visualization allows you to view the number of flights for each day of the week, along with the percentage of on-time flights and the average delay in minutes.

You can assess the performance of flights based on the average on-time percentage. An average on-time percentage of 52% or lower is considered poor performance, while anything else is considered good performance. Create a new Performance metric that includes a conditional analysis based on the Avg On-time (%) metric. This conditional analysis can be done by creating a derived metric that uses the If conditional function.

IF(([Average (On-time %)]<=0.52), "Poor", "Good")

Continue your analysis by breaking down performance by Origin Airport Name. Drag the Origin Airport Name attribute from the Datasets panel to the Rows area of the Editor panel.

If you want to qualify the values of a metric into more than two groups, create a derived metric using the Case function. Modify the Performance metric to use an expression that qualifies performance into three groups rather than two. The Excellent group denotes airports that have an average on-time percentage greater than 60%.

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

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. You can assess the performance of flights based on both the average on-time percentage and the average delay in minutes. Having an average on-time percentage of 52% or lower combined with an average delay time of 15 minutes or greater is considered poor performance, while anything else is considered good performance. Create a new Performance metric that includes a conditional analysis based on the Average (On-time %) metric and the Average (Delay (min)) metric.

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

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 appear 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.

Perform Conditional Calculations with Derived Metrics

  1. Open the dashboard you want to modify.

  2. Create a derived metric and reserve it for future use in a visualization. In the Datasets panel, click More to the right of the dataset in which you are creating a derived metric and choose Create Metric.

    The Metric Formula Editor opens.

  3. Enter a name for the metric in the Metric Name field.
  4. In the Functions pane, search for the If or Case functions.
  5. In the Functions pane, hover over the If or Case function and click Edit.
  6. Enter the values to use as arguments. If you are editing the If function, use the values in step 7. If you are editing the Case function, use the values in step 8.
  7. The syntax for the If function is IF(Condition, TrueResult, FalseResult) where:

    8. 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 ("").

  8. The syntax for the Case function is Condition(Condition1, ReturnValue1, Condition2, ReturnValue2,..., DefaultValue) where:

    9. 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 ("").

  9. Click Add to add the function to the metric. The function, including the arguments you provided, appears in the right pane of the Metric Formula Editor.
  10. Click Validate to check if the metric expression is valid.
  11. Click Save.

Check out the video below to learn how to use case functions.

Related Topics

Introduction to Metrics

Introduction to Derived Metrics

Create a Derived Metric Using the Metric Function Editor

Create a Derived Metric On a Grid Using a Shortcut

Create a Derived Metric by Combining Two Metrics with an Operator

Create a Derived Metric by Combining Multiple Metrics

Create a Derived Metric by Selecting the Aggregation Function

Create a Derived Metric from an Attribute

Create a Derived Metric from Scratch Using the Metric Formula Editor

Change the Aggregation and Subtotal Behavior for a Derived Metric

Provide Statistical Analysis from R Analytics

Edit Derived Metrics from the Dashboard

Rename Derived Metrics from the Dashboard

Delete Derived Metrics from the Dashboard