Formula Operators

Formula operators apply if/then/else conditions in Invoca formulas, returning true, false, or a predetermined value.

Formula operators let you apply if/then/else conditions in your formulas. Use operators in your formulas so they return true, false, or a predetermined value.

Formula operators

Operator	Description	Example
`and`	Returns `true` when both conditions are `true`, otherwise `false`. Not available for row-level security (RLS) formulas	`(1 = 1) and (3 > 2) = true`
`if...then...else`	Conditional operator. Allows multiple clauses	`if (cost > 500) then 'flag' else 'approve'`
`ifnull`	Returns the first value if it isn’t `null`, otherwise the second value	`ifnull (cost, 'unknown')`
`in`	Takes a column name and a list of values; returns `true` if the column value matches one of the values	`state in { 'texas' , 'california' }`
`isnull`	Returns `true` if the value is `null`	`isnull (phone)`
`not`	Returns `true` if the condition is `false`, otherwise `false`	`not (3 > 2) = false`
`not in`	Takes a column name and a list of values; returns `true` if the column value doesn’t match any of them	`state not in { 'texas', 'california' }`
`or`	Returns `true` when either condition is `true`, otherwise `false`	`(1 = 5) or (3 > 2) = true`

Calculate the conditional sum

Calculating the conditional sum is useful when you want to see, for example, the total revenue for a product by region. Conditional sum formulas follow this syntax: if (some condition) then (Measure) else 0. You can also use a sum_if formula, for example:

if ( product = shoes ) then revenue else 0

The following example shows how to figure out the number of customers who bought both products — in this case an iPad and a Galaxy tablet — and then the revenue generated by both:

Create the formula:
ipadcount = sum ( if ( product = 'ipad' ) then 1 else 0 ) > 0
This gives you the number of iPads that were bought.
Create another formula:
galaxycount = sum ( if ( product = 'galaxy' ) then 1 else 0 ) > 0
This gives you the number of Galaxy tablets that were bought.
Using nested formulas, combine these two formulas:
f1 = ipadcount + galaxycount
Search using the f1 formula to find the revenue generated by both products.

Getting Started

Implementation & The Invoca Tag

Campaign & Call Handling

Insights & Analytics

Formula Operators