Additional Functions for Formulas

In CRM Analytics, these versatile functions can be used with the data types text, number, date_only, and datetime to build logic into your formulas.

Additional Functions for Formulas

When entering a formula use the API name of the column in the expression, not the label. The parameters section is surrounded by parentheses.

The case function handles if/then logic for formulas. This function can be used with TEXT, NUMBER, DATE_ONLY, and DATETIME data. The syntax is:

case primary_expr(optional)
	when condition
	then result_expr
	... 
	else 
	default_expr
end

The syntax includes these keywords and parameters:

case and end keywords begin and close the expression
when and then keywords define a conditional statement. You can include one or more conditional statements.
condition is a logical expression that can be evaluated to true or false. Construct this expression with any values, identifiers, logical operator, comparison operator, or scalar functions (including date and math functions) supported by EA SQL. Example:
```
eg xInt < 5 or  price > 1000 and price <= 2000
```
result_expr is an expression that can be evaluated by the EA SQL engine. You can include values, identifiers, and scalar functions, including date and math functions. The expression can evaluate to any data type, but the data type must be consistent among all conditional expressions. So if result_expr is of NUMERIC type, then result_expr2 … result_exprN must also be of NUMERIC type. Example:
```
abs(price)
```
else allows a default expression to be specified. The else statement must follow the conditional when/then statement. There can be only one else statement. This parameter is optional.
default_expr is an expression that can be evaluated by the EA SQL engine. The outcome if the condition expression evaluates as false.

Here’s a case function example. Type is a field in the dataset. “New Business” and “Existing Business” are values of the Type field.

case 
    when Type = 'New Business'
    then 'NB'
    when Type = 'Existing Business' 
    then 'EB'
    else
    Type
end

The coalesce function returns the first non-null value in an expression. Otherwise, returns null. This function can be used with TEXT, NUMBER, DATE_ONLY, and DATETIME data. The syntax is: coalesce(expr1,expr2)

The syntax includes these parameters:

expr1 is the first field or text string to search for a non-null value
expr2is the next field or text string to search for a non-null value