Case functions Microstrategy

Case functions Microstrategy

Case functions return specified data in a SQL query based on the evaluation of user-defined conditions. In general, a user specifies a list of conditions and corresponding return values.

Case

This function evaluates multiple expressions until a condition is determined to be true, then returns a corresponding value. If all conditions are false, a default value is returned. Case can be used for categorizing data based on multiple conditions. This is a single-value function.

Syntax

Case (Condition1, ReturnValue1, Condition2,ReturnValue2,..., DefaultValue)




Example

Case(([Total Revenue] < 300000), 0, ([Total Revenue] < 600000), 1, 2)




sum(Case (Day@DESC in (“Sat”,”Sun”), Sales, 0) {~+}
Sum(Case(Category@DESC In("Books","Electronics"),Revenue,0)){~+}

CaseV (case vector)

CaseV evaluates a single metric and returns different values according to the results. It can be used to perform transformations on a metric. For example, if provided a list of values ranging from 1 to 12, CaseV might return January for a value of 1, February for a value of 2, etc. This is a single-value function.

Syntax

CaseV (Argument, Value1, Result1, Value2, Result2, ..., DefaultResult)




Example

CaseV ([Unit Profit], 2, 200, 3, 300, 1000000)




Coalesce

Returns the value of the first non-null argument. Coalesce can be used to identify data in tables that may not be fully populated or in metric definitions.

Syntax

Coalesce (Argument1, Argument2,..., ArgumentN)

Where:

The arguments for the Coalesce function can be any expression that can be evaluated as null or not null.

IF

Returns a value if the specified condition is TRUE; otherwise, a default value is returned. This is a single value function.

Syntax

IF (Condition, TrueBranch, FalseBranch)

Example

A metric is defined as:

IF ((Total Revenue < 300000), 0, 1)