KB7127: Case and CaseV functions in the MicroStrategy Analytical Engine

 
The Case function takes an expression or multiple expressions as input and returns a specified value when any of these conditions passes. If no condition passes, then a default value is returned. The syntax of the Case function is:
Case(expr1, v1, expr2, v2, expr3, v3, ………………., exprN, vN, defval) where exprI, I=1, ..N are valid expressions and vI, I =1,…N and defval are numeric values. vI and defval can be other metrics.
 
For example, the following are valid case expressions used as metric definitions:

1. Case(( > ), 0, 1)
2. Case(( > ), 0, ( < 20000), 1, 2)
3. Case(( < 100), 0, ( < 200), 1, ( < 300), 2, ( < 1000), 3, ( < 1250), 4, 5)
4. Case(Dollar Sales > 2000, Profit, 1)

 
The case function is equivalent to a pseudo-code such as:
If(expr1) return(v1);
Elseif(expr2) return(v2);
.
.
.
Else return(defval);
 
The CaseV function takes a numeric value M1 as input and compares it to another numeric value V1. If M1 equals V1, then a specified value can be returned. If M1 does not equal V1, then M1 is compared to the next value V2 in the list. If M1 does not equal any of the values in the list, then it returns a default value.
 
The syntax for the CaseV function is:
CaseV(M1, v1, ret1,v2, ret2,……., vN, retN, defval) where vI, retI, I =1,…N and defval are numeric values. vI, retI and defval can be other metrics.
 
The CaseV function is equivalent to a pseudo-code such as:
Switch(M1) {
Case v1: return(ret1)
Case v2: return(ret2)
.
.
Case vN: return(retN)
}
 
For example, the following are valid CaseV expressions used as metric definitions:

1. CaseV(, 48, 4, 306, 5, 6)
2. CaseV(, 48, 4, 306, 5, , 6, 7)
3. CaseV(case(M1 > 2000, 300, 400), 300, 1, 400, 2, 3)
4. CaseV(, 48, 4, 5)