Manage calculated fields

Who can use this feature?

  • All roles with access level "high"
  • Available on all plans

Calculated fields bring together two or more data points into an aggregate (total), ratio (%), average (mean), rank (min/max), duration, or other calculation.

Suggested uses

Calculated fields enhance your ability to measure, analyze, and target customer performance. Use calculated fields in playbooks, weighted health, and journeys to answer questions like:

  1. What is the average value of all open opportunities on my accounts?
  2. What is the usage rate on my account (% of seats used)?
  3. When was the last renewal call I had with this customer?
  4. How many days since the initial kick-off call on my account?
  5. What is the event attendance rate of my contacts?

Types of functions

The following function types are available. All functions are "pre-built functions" with the exception of custom formula:

Function Description
Single functions
Uses fields from the same object; available for all objects
Percentage Compare two fields by dividing a numerator by a denominator, multiplied by 100
Duration Count the specified number of days for a date field (since, until, between)
Custom formula Create arithmetic formulas on numeric data (addition, subtraction, multiplication, and division)
Relational functions (aggregations)
Aggregates fields across different objects; available for objects that have a 1:Many relationship (e.g., account, opportunity, or contact)
Sum Aggregate a numeric field from one object onto another object (e.g., sums values, not # of rows)
Count Counts the number of records from one object that is associated with another object. Also applies to product usage fields.
Average Average a numeric field from one object onto another object
Rank (min/max) Display the maximum or minimum value of a field across a set of records from one object onto another. Also applies to product usage fields.
Relational functions (display)
Looks up data from a primary object to display on a secondary object; available for objects that have a Many:1 relationship (e.g., all objects except account)
Lookup Display read-only data from a primary object on a related secondary object.

 

1:Many relationships

Relational functions (aggregations) are available for objects that have a 1:Many relationship (e.g., account, opportunity, or contact).

You can aggregate fields or records from any of the related objects (many records) onto the primary object they relate to (1 record):

New_with_Zendesk.jpg

Many:1 relationships

Relational functions (display) are available for objects that have a Many:1 relationship (e.g., all objects except account).

  • Display a field from the account object on any related objects: Opportunity, contact, additional, notes, tasks, goals
  • Display a field from the contact object on related objects: Notes, tasks
  • Display a field from the opportunity object on related object: Notes

Lookup_Field.png

Additional objects must be tied to the account object to use a lookup calculated field.

Create a new calculated field (pre-built)

Once you create a new calculated field, you cannot edit the field configuration; this allows Catalyst to maintain field history.

  1. Navigate to Settings > Object Configuration.
  2. Open one of your objects (e.g., accounts).
  3. Click the Calculated Fields tab.
  4. Click + Add Calculated Field.
  5. Choose Pre-built Functions.
    The availability of functions depends on the current object (see function types).
  6. Enter a field name and description.
  7. Configure the formula you want to use (see below).

    Calculated fields are case-sensitive. If you use a text filter with a "contains" operator, be sure to use the proper case for your input.

  8. Click Save.
    Calculated fields can take up to 24 hours to calculate, depending on the data source. See more cautions.

 

Sum

Aggregates a numeric field from one object onto another object
Example: SUM the value of all opportunities on an account where close probability is >25%

If no records exist, the calculated value will be null. For example, an account without open opportunities with close probability >25% will show null rather than $0. 

  • Associated object: Choose an associated object. If you choose the current object, additional options are available for summing a value over a timeframe (e.g., daily logins over 30 days).
    • Type: If parent/child relationships are configured, you can optionally choose to sum over time (past values of a single field from the current object) or across child accounts (current value of a single field across child records).
    • Timeframe: Choose the timeframe to measure (e.g., last X days). 

      Be mindful that applying a time frame of “days” at the field level and applying a second time-based aggregate layer on a historical trend module, may produce unexpected results.

  • Filter: Optionally add a filter to narrow down the record set.
  • Field: Choose the numerical field to sum (e.g., integer, currency, decimal). Can be basic or a calculated field from the associated object.

 

Count

Count the number of records from one object that is associated with another object.
Example: Count the number of main contacts on an account

If no records exist, the calculated value will be 0. For example, an account without any main contacts will show 0 rather than null. 

  • Associated object: Choose an associated object for which to count records.

    If you have a product usage integration configured via Mixpanel, Pendo, or Segment, you can choose from a "Product usage" object. Learn more about configuring product usage calculated fields.

  • Filter: Optionally add a filter to narrow down the record set. 

 

Average

Average a numeric field from one object onto another object.
Example: Average the value of open opportunities on an account

The denominator used is the count of records where a value is present (i.e., not null). If no values are present, the denominator cannot be 0 and therefore the result will be null.

  • Associated object: Choose an associated object. If you choose the current object, additional options are available for averaging a value over a timeframe (e.g., daily logins over 30 days).
    • Type: If parent/child relationships are configured, you can optionally choose to average over time (past values of a single field from the current object) or across child accounts (current value of a single field across child records).
    • Timeframe: Choose the timeframe to measure (e.g., last X days). 

      Be mindful that applying a time frame of “days” at the field level and applying a second time-based aggregate layer on a historical trend module, may produce unexpected results.

  • Filter: Optionally add a filter to narrow down the record set. 
  • Field: Choose the numerical field to average (e.g., integer, currency, decimal). Can be basic or a calculated field from the associated object.

 

Percentage

Compare two fields by dividing a numerator by a denominator, multiplied by 100.
Example: Find the rate of seats used on an account out of total purchased

If the numerator is null, the result will be 0. If the denominator is 0, the result will be null. If the denominator is null, the calculation is skipped; however, if the denominator had a value before and recently became null, the field will retain the last calculated value.

  • Numerator: Choose the numerical field to use as the numerator (e.g., integer, currency, decimal). Can be basic or a calculated field from the current object.
  • Denominator: Choose the numerical field to use as the denominator (out of). Can be basic or a calculated field from the current object.

 

Duration

Count the specified number of days for a date field.
Example: Find the number of days since the account kicked off

If the selected date field is null, the calculated value will be null. 

  • Duration: Choose the duration option to use for the calculation:
    • Days since: Number of since a specified date and today
    • Days until: Number of days from today until a specified date
    • Days between: Days between two separate dates  

      If end date is before start date, the calculation returns an absolute value (not negative).

  • Date field: Choose the date field(s) to calculate the duration (e.g., date, date/time). Can be basic or a calculated field from the current object.

    Catalyst does not measure days on a secondary object (e.g., days since last note published date for an account; however, you may be able to achieve the result through another calculation (e.g., rank calculation to find the max note published date, then create a duration calculation from that field).

 

Rank

Display the maximum or minimum value of a field across a set of records from one object onto another.
Example: Find the number of days since the account kicked off

If the selected date field is null, the calculated value will be null. 

  • Min/Max: Choose which option to use for the calculation.
  • Associated object: Choose an associated object. If you choose the current object, additional options are available for finding the rank over a timeframe (e.g., maximum number of open support requests over the last 90 days).
    • Type: If parent/child relationships are configured, you can optionally choose to rank over time (past values of a single field from the current object) or across child accounts (current value of a single field across child records).
    • Timeframe: Choose the timeframe to measure (e.g., last X days). 

      Be mindful that applying a time frame of “days” at the field level and applying a second time-based aggregate layer on a historical trend module, may produce unexpected results.

    If you have a product usage integration configured via Mixpanel, Pendo, or Segment, you can choose from a "Product usage" object. Learn more about configuring product usage calculated fields.

  • Filter: Optionally add a filter to narrow down the record set.
  • Field: Choose the numerical or date field to rank. Can be basic or a calculated field from the associated object.

 

Lookup

Display read-only data from a primary object on a related secondary object.
Examples: Look up the Customer Success Manager on the account and display the CSM's name on each record. Display the account segment and ARR as a column on the additional object to export a CSV with both data joined. Display the account's NPS on each contact record to trigger a playbook. 

Refer to Many:1 relationships for details on supported objects and fields.

  • Associated object: Choose an associated object where you want to look up the field.
  • Field: Choose the field to look up (all types except rich text and text area). Can be basic or a calculated field from the associated object.

Create a new calculated field (formula builder)

The custom formula builder allows you to create arithmetic formulas on numeric data (addition, subtraction, multiplication, and division).

Custom formulas treat null as a 0. This means that:

  • Addition and/or subtractions with a null value will result in a numeric value
  • Multiplication and/or division with a null value will result in 0; however, dividing by 0 will always result in null

After you create a new calculated field, you cannot edit the field configuration; this allows Catalyst to maintain field history.

  1. Navigate to Settings > Object Configuration.
  2. Open one of your objects (e.g., accounts).
  3. Click the Calculated Fields tab.
  4. Click + Add Calculated Field.
  5. Choose Custom Formula.
    The availability of functions depends on the current object (see function types).
  6. Enter a field name and description.
  7. Under the node panel, drag-and-drop operators into the formula builder:
    • +
    • -
    • %
    • x
    • (): Manage the order of operations (PEMDAS 🇺🇸 or BEDMAS 🇨🇦 )
    • #: Add a static numeric value to the formula builder (e.g., to multiply by 30%, add the # and type .30)
  8. Search for and drag-and-drop numerical calculated fields or object-level fields into the formula builder (e.g., integer, currency, decimal).
  9. Depending on the field types used in the formula, choose how you want to display the values:
    • Currency
    • Decimal
    • Integer
    • Percentage
  10. Use the preview to ensure your formal is formatted properly. The preview automatically generates numbers to show you the order of operations.
  11. Click Save.
    Calculated fields can take up to 24 hours to calculate, depending on the data source. See more cautions.

Permissions and limitations

  • If a field has restrictive permissions, you cannot use it in a calculated field
  • Calculated fields have default read permissions for all role types
  • Nested calculated fields can go up to three levels deep
  • Calculated fields do not sync back to Salesforce
  • There is a cumulative limit of 50 calculated fields across all objects, including reference fields

Looking for inspiration?

Here are some additional use cases for common objects.

Use Case Operator Description
Account object
Total revenue, pipeline, and activities SUM
  • SUM opportunity ARR where close date is within current fiscal quarter
  • SUM opportunity ARR where open equals true
  • SUM opportunity upsell amount were close date is within the current fiscal year
  • SUM task hours where complete equal true
Amount of contacts created, tasks actioned, calls made COUNT
  • COUNT of contacts where main POC is true
  • COUNT of contacts marked as decision maker
  • COUNT of notes where category equals escalation
  • COUNT of tasks were status is completed last 90 days
  • COUNT of conversations where created date in the current quarter
Average renewal, engagement, or call metrics AVERAGE
  • Average ARR of opportunity where type equals renewal
  • Average open opportunity probability
  • Average time to complete cases within the current fiscal year
  • Average duration (min) for conversations in the current quarter
Seat utilization and NPS score PERCENTAGE
  • Rate of seats used over seats purchased
  • Rate of total promoters out of total survey sent
  • Rate of total tasks completed over total tasks created within current fiscal year*
    *Must create a COUNT field first
  • Rate of total open tasks completed over total tasks created
Days since account milestones DURATION
  • Days since account kick-off call
  • Days since last account opportunity was marked as closed
  • Days between account contract signature and kick-off
Max days since last activity RANK
  • Rank (max) notes published where category equals meeting
  • Rank (max) date a contact was added
  • Rank (max) tasks completed where category equals gift
Opportunity object
Activities associated with open opportunities COUNT
  • Count notes were category is renewal within the last quarter
Renewal growth rate PERCENTAGE
  • Rate of opportunity renewal invoice ARR out of previous contract ARR
Countdown until renewal DURATION
  • Days since opportunity subscription start date
  • Days to opportunity close date where category equals renewal
Data of last renewal activity RANK
  • Rank (max) start date where task category equals renewal AND status is open
Contact object
Activities associated with contacts COUNT
  • Count of notes where category contains "Escalation"
  • Count of notes where category equals Marketing Ready
Contact advocacy PERCENTAGE
  • Rate of contact field events attended out of field events invited
  • Rate of contact field reviews submitted out of field reviews requested
Days since last critical activity DURATION
  • Days since last meeting notes logged*
    *Must create a rank calculated field first
  • Days since last escalation notes within the current fiscal year
Last date a gift was sent RANK
  • Rank (max) notes published at a date where category equals gift given

Was this article helpful?

0 out of 0 found this helpful

Have more questions? Submit a request