Skip to main content

Formula operators

Formula operators help you perform conditional calculations within your data. By using these operators, you can apply if/then/else logic to return specific values or simple true/false results based on your unique criteria.

Written by Jonathan Crouch
Updated over a month ago

Formula operators

The available operators include:

and

  • Returns true when both conditions are true, otherwise returns false.

  • Example

    • (amount > fee) and (amount > net amount) = true

Not available for row-level security (RLS) formulas.

if…then…else

  • Conditional operator. Allows for multiple clauses.

  • Example

    • if (amount > 500) then 'large' else 'small'

ifnull

  • Returns the first value if it is not null, otherwise returns the second value.

  • Example 

    • ifnull (amount, 0)

in

  • Takes a column name and a list of values. It checks each column value against the list of values in the formula, and returns true if the column value matches one of the values in the formula.

  • Example 

    • donor address state in { 'texas' , 'california' }

isnull

  • Returns true if the value is null (not 0, but unknown).

  • Example 

    • isnull (fee)

not

  • Returns true if the condition is false, otherwise returns false.

  • Example

    • not (amount > net amount) = false

not in

  • Takes a column name and a list of values. It checks each column value against the list of values in the formula, and returns true if the column value does not match any of the values in the formula.

  • Example 

    • donor address state not in { 'texas' , 'california' }

or

  • Returns true when either condition is true, otherwise returns false.

  • Example

    • (amount = net amount) or (amount > fee) = true

Calculate the conditional sum

Using a conditional sum is a great way to isolate specific data points, such as viewing total giving amounts for all successful gifts.

These formulas generally follow this syntax: if (some condition) then (measure) else 0. You can also utilize a sum_if formula. This approach allows you to limit your search results effectively without needing to add a formal column filter. For example: if ( is successful ) then amount else 0 (meaning, if the gift was successful).

The following example demonstrates how to determine what the total giving amount would be if the giving transactions that failed actually didn't fail.

  1. Create the following formula in the Formula Builder and name it "Is Successful."

    1. if ( is successful ) then amount else 0

    2. This formula will provide you with the dollar amount of successful gifts.

  2. You can then create another formula that looks like this and name it "Is Transaction Failed."

    1. if ( is transaction failed ) then amount else 0

    2. And this formula will provide you with the dollar amount of transactions that failed.

  3. Using nested formulas, you can combine these two formulas. For example:

    1. f1 = Is Successful + Is Transaction failed

  4. Now, you can search using the f1 formula to find out the giving amount you would have if transactions hadn't failed.

Related Articles
Filter on null, blank, or empty values
Formulas
Add a Formula to a Chart
Aggregate formulas
Nested formulas
Did this answer your question?