Visualizing Data and Building Reports in Oracle Analytics Cloud
Reference
Expression Editor Reference
Conditional Expressions
Conditional Expressions
You use conditional expressions to create expressions that convert values.
The conditional expressions described in this section are building blocks for creating expressions that convert a value from one form to another.
Follow these rules:
In
CASE
statements,
AND
has precedence over
Strings must be in single quotes.
Evaluates each
WHEN
condition and if
satisfied, assigns the value in the corresponding
THEN
expression.
If none of the
WHEN
conditions are
satisfied, it assigns the default value specified in the
ELSE
expression. If no
ELSE
expression is specified, the system automatically adds an
ELSE NULL
.
Note
: See
Best Practices for using CASE
statements in Analyses and
Visualizations
.
Also referred to as
CASE (Lookup)
. The value of the first
expression is examined, then the
WHEN
expressions.
If the first expression matches any
WHEN
expression, it assigns the value in the corresponding
THEN
expression.
If none of
the
WHEN
expressions match, it assigns the default
value specified in the
ELSE
expression. If no
ELSE
expression is specified, the system
automatically adds an
ELSE NULL
.
If the first expression matches an expression in multiple
WHEN
clauses, only the expression following the
first match is assigned.
Note
See
Best Practices for
using CASE statements in Analyses and
Visualizations
.
CASE expr1 WHEN expr2 THEN expr3 ELSE expr4
Analyses and Visualizations
When using CASE statements in reports and workbooks, consider the report
columns and the order of aggregation because these affect how expressions and sub-totals are
calculated.
In general, when using CASE expressions, make sure that any columns
used in the expression are included in the report.
If the order of aggregation is important, then change the report aggregation rule
from Default to Sum.
If a condition has to be evaluated before the aggregation is
computed (for example, if the column filtered isn't displayed in the report),
then use the FILTER function.
Example
This example use data for Brand, Product Type, and Revenue.
Description of the illustration case-best-practice-data.png
To apply a condition to set Camera revenue to 0, create the following
conditional expression:
CASE WHEN Product_Type = ‘Camera’ THEN 0 ELSE Revenue
END
.
When Product_Type is excluded from the report, data isn't available to
evaluate the expression because it contains Product_Type, and the results are:
Description of the illustration case-best-practice-results-1.png
By adding PRODUCT_TYPE to the report and setting report aggregation to SUM, the result
aggregates after the base level values are calculated:
Description of the illustration case-best-practice-results-2.png
An alternative to using CASE is to use a filter expression: FILTER(Revenue using
Product_Type != ‘Camera’). The expression is calculated independently of report columns,
and aggregations are applied after calculation:
Description of the illustration case-best-practice-results-3.png
