Logic Functions

ProDiver supports a variety of functions to perform calculations on your data. Data is passed as arguments to these functions. Arguments can be columns, variables (parameters), or constants. Functions can be nested and are evaluated inside to out. The following logic functions are available:

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])