Logic Functions

Used with IF. Computes a logical AND of two or more Boolean expressions. If all expressions are true, the calculation returns TRUE. Otherwise, it returns FALSE.
Syntax: and(bool_expr1, bool_expr2, bool_expr3, …)
Example: if(and(Total[Revenue]>100000,Total[Revenue]<200000),1,0)
In this example, the AND function (and(Total[Revenue]>100000,Total[Revenue]<200000) is nested in the IF function. If the revenue value is greater than 100,000 and less than 200,000, the calculation returns a 1. Otherwise, it returns a 0.

Selects between two expressions based on a condition. If the condition is true (non-zero), the calculation returns the first expression. Otherwise, the second expression is returned.
Syntax: if(condition, then expr1, else expr2 )
Example: if(Total[Revenue]>=100000,1,0)
In this example, if the total revenue is greater than or equal to 100,000, the calculation returns a 1. Otherwise, it returns a 0.
NOTE: If you use nested IF functions, all expressions must be the same type of data (numeric or case-sensitive string).

Is not a number. Indicates a value that is the result of a mathematical error, or a blank ELSE clause in an IF calculation. The calculation returns a 1 if there is a mathematical error or blank ELSE expression. Otherwise, it returns a 0.
Syntax: isnan(field)
Example: calc[total_payment]+ if(isnan(calc[improvement_pay]),0,calc[improvement_pay])
In this example, the total_payment calculation is defined to handle an invalid calc. That is, the improvement_pay (which is a ratio) could be invalid. If so, add a zero, otherwise add the improvement_pay value.

Indicates a value that is blank because the underlying data does not exist in the cBase or model. The calculation returns a 1 if the value is null or blank. Otherwise, it returns a 0.
Syntax: isnull(field)

Used with IF. Computes a logical NOT of the Boolean expression. If the expression is true, the calculation returns FALSE. Otherwise, it returns TRUE.
Syntax: not(bool_expr)
Example: if(not(Actual_Dollars=100000),1,0)
In this example, the NOT function (not(Actual_Dollars=100000) is nested in the IF function. If the value of actual dollars is not 100,000, the calculation returns a 1. Otherwise, it returns a 0.

Used with IF. Computes a logical OR of two or more Boolean expressions. If any of the expressions are true, the calculation returns TRUE. Otherwise, it returns FALSE.
Syntax: or(bool_expr1, bool_expr2, bool_expr3, …)
Example: if(or(Total[Decimal Cases]>50000,Total[Revenue]>5000000),1,0)
In this example, the OR function (or(Total[Decimal Cases]>50000,Total[Revenue]>5000000) is nested in the IF function. If the total decimal cases is greater than 50,000 or the total revenue is greater than 5,000,000; the calculation returns a 1. Otherwise, it returns a 0.

Indicates if a number is positive, negative, or 0. A positive number returns a 1, a negative number returns a -1, and a zero returns a 0.
Syntax: sign(number)
Example: sign(Total[Revenue])