Building an If-Then-Else Calculated Expression
Your models can include calculated measures that use if-then-else expressions.
For example, you can define the following conditional expression:
if ("Net Income"<100000) then ("Gross Profit"*1.25) else
NULL
You can also define an if-then-else calculation to avoid division by zero:
if ("Revenue" != 0) then ("Quantity"
/ "Revenue") else NULL
Procedure
1. In the Expression Editor, begin your calculated measure definition by clicking
the if operator.
An opening parenthesis is inserted next.
2. Enter the rest of the expression as follows:
v Type a conditional expression that resolves to True or False.
v Double-click a closing parenthesis.
v After the automatically inserted expression "then (", type the result to show
if the boolean expression is True.
v Double-click a closing parenthesis.
v After the automatically inserted expression "else (", type the result to show
if the boolean expression is False.
v Double-click a closing parenthesis and click OK.
If your expression is valid, the definition is saved for that measure.
3. If an error appears, try again, using a mathematically correct form.
Tip: Unlike calculated columns, calculated measures support isnull(
)expressions that resolve to True, which is the condition that arises with a
missing value. You can use this capability to avoid divide-by-zero overflow
errors.
4. Build the cube, open it in your reporting application, and confirm that the
results correctly reflect the if-then-else condition you were trying to model.
Functions
A function is a subroutine that returns a single value. You can use functions to
create calculations and conditions to filter data. Functions are similar to operators
in that they manipulate data items and return a result.
Functions differ from operators in the format in which they appear with their
arguments. This format allows them to operate with zero, one, two, or more
arguments:
function (argument, argument, ...)
Functions are of these general types:
Function Description
Date Functions Accepts numeric input and returns a value that is a date.
Appendix F. IBM Cognos Transformer Expression Editor 357
To Next Page
Loading...
Need help?
General