Creating Calculations – Tips and General Guidelines

General Rule: Limit Nested Calculations

Avoid referencing the same calculated field multiple times within another calculation, as this can lead to performance issues. This practice, known as creating a nested calculation, should be minimized to improve efficiency. However, referencing a terminal field multiple times in a calculation shouldn’t degrade performance.

Example:

Imagine you have a calculated field that uses a complex formula to find Twitter handles in tweets, called Twitter Handle. Each handle starts with the ‘@’ symbol (e.g., @user). To remove the ‘@’ symbol, you might use:

RIGHT([Twitter Handle], LEN([Twitter Handle]) - 1)

This calculation references Twitter Handle twice, causing it to perform the calculation twice for each record. To optimize this, use MID:

MID([Twitter Handle], 2)

Tip 1: Use CASE Expressions or Groups for Multiple Equality Comparisons

If your calculation uses a field multiple times with OR functions, it can slow down performance. Instead, use a CASE expression or a group.

Example:

Original calculation using multiple OR functions:

IF [Person (calc)] = 'Henry Wilson'
OR [Person (calc)] = 'Jane Johnson'
OR [Person (calc)] = 'Michelle Kim'
OR [Person (calc)] = 'Fred Suzuki'
OR [Person (calc)] = 'Alan Wang'
THEN 'Lead'
ELSEIF [Person (calc)] = 'Susan Nguyen'
OR [Person (calc)] = 'Laura Rodriguez'
OR [Person (calc)] = 'Ashley Garcia'
OR [Person (calc)] = 'Andrew Smith'
OR [Person (calc)] = 'Adam Davis'
THEN 'IC'
END

Solution 1: Use a CASE expression:

CASE [Person (calc)]
WHEN 'Henry Wilson' THEN 'Lead'
WHEN 'Jane Johnson' THEN 'Lead'
WHEN 'Michelle Kim' THEN 'Lead'
WHEN 'Fred Suzuki' THEN 'Lead'
WHEN 'Alan Wang' THEN 'Lead'
WHEN 'Susan Nguyen' THEN 'IC'
WHEN 'Laura Rodriguez' THEN 'IC'
WHEN 'Ashley Garcia' THEN 'IC'
WHEN 'Andrew Smith' THEN 'IC'
WHEN 'Adam Davis' THEN 'IC'
END

Solution 2: Create a group instead of a calculated field

Tip 2: Use REGEXP for String Calculations

For performance, replace multiple string calculations with a single REGEXP expression.

Example 1: CONTAINS

Original calculation:

IF CONTAINS([Segment (calc)],'UNKNOWN')
OR CONTAINS([Segment (calc)],'LEADER')
OR CONTAINS([Segment (calc)],'ADVERTISING')
OR CONTAINS([Segment (calc)],'CLOSED')
OR CONTAINS([Segment (calc)],'COMPETITOR')
OR CONTAINS([Segment (calc)],'REPEAT')
THEN 'UNKNOWN'
ELSE [Segment (calc)]
END

Solution:

IF REGEXP_MATCH([Segment (calc)], 'UNKNOWN|LEADER|ADVERTISING|CLOSED|COMPETITOR|REPEAT') THEN 'UNKNOWN'
ELSE [Segment (calc)]
END

Example 2: STARTSWITH

Original calculation:

IF STARTSWITH([Segment (calc)],'UNKNOWN')
OR STARTSWITH([Segment (calc)],'LEADER')
OR STARTSWITH([Segment (calc)],'ADVERTISING')
OR STARTSWITH([Segment (calc)],'CLOSED')
OR STARTSWITH([Segment (calc)],'COMPETITOR')
OR STARTSWITH([Segment (calc)],'REPEAT')
THEN 'UNKNOWN'

Solution:

IF REGEXP_MATCH([Segment (calc)], '^(UNKNOWN|LEADER|ADVERTISING|CLOSED|COMPETITOR|REPEAT)') THEN 'UNKNOWN'
ELSE [Segment (calc)]
END

(Note the ^ symbol used.)

Example 3: ENDSWITH

Original calculation:

IF ENDSWITH([Segment (calc)],'UNKNOWN')
OR ENDSWITH([Segment (calc)],'LEADER')
OR ENDSWITH([Segment (calc)],'ADVERTISING')
OR ENDSWITH([Segment (calc)],'CLOSED')
OR ENDSWITH([Segment (calc)],'COMPETITOR')
OR ENDSWITH([Segment (calc)],'REPEAT')
THEN 'UNKNOWN'
ELSE [Segment (calc)]
END

Solution:

IF REGEXP_MATCH([Segment (calc)], '(UNKNOWN|LEADER|ADVERTISING|CLOSED|COMPETITOR|REPEAT)$') THEN 'UNKNOWN'
ELSE [Segment (calc)]
END

(Note the $ symbol used.)

Tip 3: Use REGEXP for String Manipulations

Use regular expressions for complex string manipulations, which are often shorter and more efficient.

Example 1

Original calculation to remove protocols from URLs:

IF (STARTSWITH([Server], "http://")) THEN
MID([Server], LEN("http://") + 1)
ELSEIF(STARTSWITH([Server], "https://")) THEN
MID([Server], LEN("https://") + 1)
ELSEIF(STARTSWITH([Server], "tcp:")) THEN
MID([Server], LEN("tcp:") + 1)
ELSEIF(STARTSWITH([Server], "\\")) THEN
MID([Server], LEN("\\") + 1)
ELSE [Server]
END

Solution:

REGEXP_REPLACE([Server], "^(http://|https://|tcp:|\\\\)", "")

Example 2

Original calculation to return the second part of an IPv4 address:

IF (FINDNTH([Server], ".", 2) > 0) THEN
MID([Server],
FIND([Server], ".") + 1,
FINDNTH([Server], ".", 2) - FINDNTH([Server], ".", 1) - 1
)
END

Solution:

REGEXP_EXTRACT([Server], "\\.([^\\.]*)\\.")

Tip 4: Avoid Using Sets in Calculations

Instead of using sets in a calculation, use an equivalent calculated field.

Example:

Original calculation using a set:

IF ISNULL([Customer Name]) OR [Top Customers (set)] THEN [Segment] ELSE [Customer Name] END

Solution 1:

Create a calculated field equivalent to the set:

CASE [Customer Name]
WHEN 'Henry Wilson' THEN TRUE
WHEN 'Jane Johnson' THEN TRUE
WHEN 'Michelle Kim' THEN TRUE
WHEN 'Fred Suzuki' THEN TRUE
WHEN 'Alan Wang' THEN TRUE
ELSE FALSE
END

Solution 2:

Create a group and modify the calculation:

IF ISNULL([Customer Name]) OR [Top Customers (group)] = 'IN' THEN [Segment] ELSE [Customer Name] END

Tip 5: Don’t Use Sets to Group Data

Use groups instead of sets to combine related members in a field.

Example:

Original calculation:

IF [Americas Set] THEN "Americas"
ELSEIF [Africa Set] THEN "Africa"
ELSEIF [Asia Set] THEN "Asia"
ELSEIF [Europe Set] THEN "Europe"
ELSEIF [Oceania Set] THEN "Oceania"
ELSE "Unknown"
END

Sets are not always exclusive and can’t always be translated to groups. Instead, use groups for combining related members.