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.
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 Function Plug-In Wizard.
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.
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.
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, 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 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.
You are
comfortable working with functions and metric levels.
You must
have Edit Dashboard and Run Dashboard privileges.
To
create a derived metric from scratch:
Click the name of the dashboard
or document to open it.
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 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. Note:
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.
Type a name for the metric in
the Metric Name field.
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.
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 add a function to the
metric, perform the following steps:
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.
Hover
the cursor over the name of the function, then click Edit.
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:
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.
If the Function Parameters icon
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 . 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.
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.
If you selected a non-grouping
function, such as data mining, date, 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:
For
each argument listed, type a value to use as input values of
the function.
For
each parameter listed, type a value or select the parameter value
from the drop-down list.
When
finished, click Add to add
the function to the metric. The function, including the arguments
you provided, is displayed in the editor pane.
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.
When you have completed the metric
formula, click Validate. The
Expression Editor displays a status message below the metric expression,
indicating whether or not the new metric expression is correct. When
the message displays as Valid Metric Formula, the metric expression
is correct.
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.
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.
Prerequisite
You must have Edit Dashboard and Run Dashboard privileges.
To perform conditional calculations with
derived metrics:
Click the name of the dashboard
or document to open it.
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. Note: IFrom the Datasets
or Dataset Objects panel, right-click the dataset to add the metric
to, and select New Metric.
The Metric Formula Editor opens.
Type a name for the metric in
the Metric Name field.
From the Functions
list to the left, browse to and select the If or Case function to
use to create the derived metric.
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.
Type the values to use as arguments,
as described below:
The syntax
for the If function is IF(Condition,
TrueResult, FalseResult) where:
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 ("
").
When finished, click Add
to add the function to the metric. The function, including the arguments
you provided, is displayed in the editor pane.
Click Validate.
The Formula Editor displays a status message below the metric expression,
indicating whether or not the new metric expression is correct. When
the message displays as Valid Metric Formula, the metric expression
is correct.
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
Creating
a metric based on existing objects: Derived metrics
Editing
or deleting derived metrics in a dashboard
Creating
a filter for the data on a visualization
Thresholds Editor
for dashboards
_____________________________
Copyright © 2019, Oracle and/or its affiliates. All rights reserved.
Legal Notices