Formulas

You are here:

Formulas

Use the formula node to create multiple formulas and store the results of each formula in a new field. Create multiple formulas in a single node and use the formula results in subsequent nodes. Use the compute relative transformation option to determine the trends of specific fields.

Required Editions

View supported editions.

Before you create a formula, specify the alias, data type, and format of the derived field. The derived field can be of type Date, Date/Time, Number, or Text.

To create a formula, pick a field, an operator, and a function in any combination. You can also pick an input variable as a field. After you create a formula, check if the syntax is valid. The result of this node includes all the fields from the source node and the formula fields you create. If a field that’s used in a formula is removed from the source node, ensure that you manually remove the field from the formula.

Note To calculate the total number of formula fields that you can create in a formula node, add the fields in the source node and custom formulas.

Example To calculate the average annual revenue, the formula is {AnnualRevenue}/12. The formula field is of type Number.

Math Functions

Function	Description	Supported Runtime	Syntax	Example Syntax
ABS	Calculates the absolute value of a number. The absolute value of a number is the number without its positive or negative sign.	CRM Analytics and Data Cloud	`ABS(n)` n is a number or field that you want to determine the absolute value of.	`ABS({AnnualRevenue} - {Target_Revenue__c})`
CEIL	Rounds a number up to the nearest integer, away from zero if negative.	CRM Analytics and Data Cloud	`CEIL(n)` n is a number or field that you want to round up to the nearest integer.	`CEIL({NumberOfEmployees} / 15)`
EXP	Returns a value for e raised to the power of a number you specify.	CRM Analytics and Data Cloud	`EXP(n)` n is a number or field to which you want to raise e.	`{AnnualRevenue} * EXP({Growth_Rate__c})`
FLOOR	Returns a number rounded down to the nearest integer. If the number is negative, it’s rounded up to the nearest integer.	CRM Analytics and Data Cloud	`FLOOR(n)` n is a number or field that you want to round down to the nearest integer.	`FLOOR({AnnualRevenue} / 5000)`
LOG	Returns the logarithm of a number in the base you specify.	CRM Analytics and Data Cloud	`LOG(m, n)` m is a number or field used as the base of the logarithm. n is a number or field that you want to take the logarithm of.	`LOG(5, {NumberOfEmployees})`
MAX	Returns the highest number from a list of numbers.	CRM Analytics and Data Cloud	`MAX(value1, value2)` value1 is the first number or field to be compared. value2 is the second number or field to be compared.	`MAX({Projected_Q1_Revenue__c}, {Projected_Q2_Revenue__c})`
MIN	Returns the lowest number from a list of numbers.	CRM Analytics and Data Cloud	`MIN(value1, value2)` value1 is the first number or field to be compared. value2 is the second number or field to be compared.	`MIN({Support_Plan_Cost__c}, {Maintenance_Fee__c})`
MOD	Returns a remainder after a number is divided by a specified divisor.	CRM Analytics	`MOD(number, divisor)` number is the number or field to be divided. divisor is the number or field that you want to divide by.	`MOD({NumberOfEmployees}, 10)`
POWER	Raises a number to the power of another number.	CRM Analytics and Data Cloud	`POWER(m, n)` m is a number or field that you want to raise to the specified power. n is a number or field that is the power that you want to raise the specified number to.	`POWER({Customer_Tier__c}, 2)`
ROUND	Returns the nearest number to a number you specify, constraining the new number by a specified number of digits.	CRM Analytics and Data Cloud	`ROUND(n[, m])` n is a number or field that you want to round. m is a number or field that specifies the number of digits to round the specified number to.	`ROUND({AnnualRevenue} * {Commission_Rate__c}, 2)`
SQRT	Returns the positive square root of a given number.	CRM Analytics and Data Cloud	`SQRT(n)` n is a number or field that you want to find the square root of.	`SQRT({Office_Area_SqFt__c})`
TRUNC	Truncates the specified number of decimal values from the number.	CRM Analytics and Data Cloud	`TRUNC(number, decimalPlaces)` number is a number or field that you want to truncate. decimalPlaces is the number of digits you want to the right of the decimal point. This parameter is required. If you don’t specify want any decimal values, use a 0.	`TRUNC({AnnualRevenue} / {NumberOfEmployees}, 0)`

String Functions

Function	Description	Supported Runtime	Syntax	Example Syntax
BEGINS	Returns true if the string starts with the specified characters.	CRM Analytics and Data Cloud	`BEGINS(string, prefix)` string is the text or field to search. prefix is the value to search for at the beginning of the field.	`BEGINS({AccountSource} , "Web")`
CONCAT	Returns a string by connecting the values of the specified columns and input strings.	CRM Analytics and Data Cloud	`CONCAT(field, num_chars)` field is the text or field from which you want to extract characters. num_chars is the number of characters you want to return.	`CONCAT({Name}, " (", {BillingCity}, ")")`
CONTAINS	Compares two arguments of text and returns TRUE if the first argument contains the second argument. If not, it returns FALSE.	CRM Analytics and Data Cloud	`CONTAINS(string, searchSting)` string is the text or field that you want to search within. searchString is the specific text you are looking for inside the string.	`CONTAINS({Description}, "High Priority")`
ENDS	Returns true if the specified string is found at the end of the column value.	CRM Analytics and Data Cloud	`ENDS(string, prefix)` string is the text or field to search. prefix is the value to search for at the end of the field.	`ENDS({Website}, ".gov")`
LENGTH	Returns the number of characters in a text.	CRM Analytics and Data Cloud	`LENGTH(field)` field is the text or field to measure.	`LENGTH({BillingPostalCode})`
LOWER	Returns the string with all letters in the specified text string to lowercase. Any characters that aren’t letters are unaffected by this function. Locale rules are applied if a locale is provided.	CRM Analytics and Data Cloud	`LOWER(string)` string is the text or field to convert to lowercase.	`LOWER({Website})`
LTRIM	Removes the specified substring from the beginning of a string. To remove leading spaces, do not specify a substring.	CRM Analytics and Data Cloud	`LTRIM(string, substr)` string is the text or field to remove the leading spaces or specified characters from. substr is the value to remove from the string. If unspecified, the transformation removes the leading spaces.	`LTRIM({Phone} ,"+")`
MD5	Returns a 32-character string that is a text representation of the hexadecimal value of a 128-bit checksum.	Data Cloud	`MD5(string))` string is the text or field for which you want to calculate the checksum.	`MD5({Name})`
RTRIM	Removes the specified substring from the end of a string. To remove trailing spaces, do not specify a substring.	CRM Analytics and Data Cloud	`RTRIM(string, substr)` string is the text or field to remove the trailing spaces or specified characters from. substr is the value to remove from the string. If unspecified, the transformation removes the trailing spaces.	`RTRIM({PhotoUrl} , ".")`
SUBSTITUTE	Substitutes new text for old text in a text string.	CRM Analytics and Data Cloud	`SUBSTITUTE(string, searchStr, replaceStr)` string is the text or field that contains the string to replace. searchStr is the string to replace. replaceStr is the value to replace the string.	`SUBSTITUTE({Phone}, ".", "-")`
SUBSTR	Returns characters from the string, starting at the specified position and of the specified length.	CRM Analytics and Data Cloud	`SUBSTR(field, pos[, len])` field is the text or field to extract the substring from. position is the starting character position of the substring. The first character in a string is position 1. If the position is negative, the position is relative to the end of the string. A position of -1 is the last character. len is the number of characters to return. If the length is 0, the output is an empty string. If the length is negative, the function returns null. This parameter is optional.	`SUBSTR({BillingPostalCode} , 1, 3)`
TEXT	Converts a value that represents a number to a text string that represents the same number.	CRM Analytics and Data Cloud	`TEXT(number)` number is the number or field to convert.	`TEXT({NumberOfEmployees}))`
TRIM	Removes the specified substring from the beginning and end of a string. To remove leading and trailing spaces, do not specify a substring.	CRM Analytics and Data Cloud	`TRIM(string, substr)` string is the text or field to remove the specified substring from. substr is the value removed from the string. This parameter is optional. If unspecified, the transformation removes the trailing spaces.	`TRIM({SourceSystemIdentifier} , " ")`
UPPER	Returns the string with all letters in the specified text string to uppercase. Any characters that aren’t letters are unaffected by this function. Locale rules are applied if a locale is provided.	CRM Analytics and Data Cloud	`UPPER(string)` string is the text or field to convert to upercase.	`UPPER({BillingPostalCode})`
UUID	Returns a newly generated unique ID.	CRM Analytics and Data Cloud	`UUID()`	`UUID()`
VALUE	Converts a text string that represents a number to a value that represents the same number.	CRM Analytics and Data Cloud	`VALUE(string)` string is the text or field to convert to number.	`VALUE({Legacy_Employee_Count__c})`

Date Functions

Function	Description	Supported Runtime	Syntax	Example Syntax
ADDDAYS	Returns a new date after adding the specified number of days.	CRM Analytics and Data Cloud	`ADDDAYS(date, 5)` date is the date or field to add days to. 5 is the number of days to add	`ADDDAYS({CreatedDate}, 14)`
ADDMONTHS	Returns the date with the specified number of months after the start date.	CRM Analytics and Data Cloud	`ADDMONTHS(date, 5)` date is the date or field to add months to. 5 is the number of months to add to add	`ADDMONTHS({CreatedDate}, 12)`
DATEDIFF	Returns the number of days between start date and end date.	CRM Analytics and Data Cloud	`DATEDIFF(endDate, startDate)` endDate is the later of the two dates you are finding the difference between. startDate is the earlier of the two dates you are finding the difference between.	`DATEDIFF({LastActivityDate} , {LastViewedDate})`
DATETIMEVALUE	Returns a year, month, day, and GMT time value.	CRM Analytics and Data Cloud	`DATETIMEVALUE(dateTimeString[,format])` dateTimeStringfield is a timestamp or field that you want to convert. format is the timestamp format pattern to follow. This parameter is optional. The function follows casting rules to a timestamp if format isn’t included.	`DATETIMEVALUE({LastModifiedDate})`
DATEVALUE	Returns a date value for a date/time or text expression.	CRM Analytics and Data Cloud	`DATEVALUE(dateString[,format])` dateStringfield is a date or field that you want to convert. format is the date format pattern to follow. This parameter is optional. The function follows casting rules to a timestamp if format isn’t included.	`DATEVALUE({LastActivityDate})`
DAY	Returns a day of the month in the form of a number from 1 through 31.	CRM Analytics and Data Cloud	`DAY(date)` date is the date field to extract the day of the month from.	`DAY({Contract_Start_Date__c})`
MONTHDIFF	Returns the number of months between start date and end date.	CRM Analytics and Data Cloud	`MONTHDIFF(endDate, startDate)` endDate is the later of the two dates you are finding the difference between. startDate is the earlier of the two dates you are finding the difference between.	`MONTHDIFF({LastActivityDate} , {LastViewedDate})`
NOW	Returns a date/time representing the current moment in UTC (GMT).	CRM Analytics and Data Cloud	`NOW()`	`(NOW() - {CreatedDate}) * 24`
TODAY	Returns the current date as a date data type.	CRM Analytics and Data Cloud	`TODAY()`	`{Contract_End_Date__c} - TODAY()`
WEEKDAY	Return the day of the week for the given date, using 1 for Sunday, 2 for Monday, through 7 for Saturday.	CRM Analytics and Data Cloud	`WEEKDAY(date)` date is the date field to extract the day of the week from.	`WEEKDAY(DATEVALUE({CreatedDate}))`

Logical Functions

Function	Description	Supported Runtime	Syntax	Example Syntax
AND	Returns a TRUE response if all values are true. Returns a FALSE response if one or more values are false.	CRM Analytics and Data Cloud	`AND(logical1, logical2, ...)` logical1 is the first logical condition that you want to check. This expression must result in a value of either TRUE or FALSE. logical2 is the second logical condition that you want to check, which must also result in a value of TRUE or FALSE.	`AND({NumberOfEmployees} > 1000, {BillingCountry} == "USA")`
BLANKVALUE	Checks whether an expression is blank. If the expression is blank, substitute_expression is returned. If the expression contains a value, that value is returned.	CRM Analytics and Data Cloud	`BLANKVALUE(expression, substitute_expression)` expression is the field or value you want to check to see if it's empty. substitute_expression is the value you want the formula to return if the expression is blank.	`BLANKVALUE({Description}, "No description provided.")`
CASE	Checks a given expression against a series of values. If the expression is equal to a value, it returns the corresponding result. If it isn’t equal to any values, it returns the else_result.	CRM Analytics and Data Cloud	`CASE(expression,value1, result1, value2, result2,...,else_result)` expression is the field or value you want to compare against a series of other values. value1 is the first value you want to check for a match with the expression. result1 is the value that is returned if the expression matches value1. value2, result2, ... is the next pair of values to check and the corresponding results to return. You can have many of these pairs. else_result is the value that is returned if the expression does not match any of the specified values.	`CASE({Industry}, "Technology", "Tech Sales Team", "Healthcare", "Healthcare & Life Sciences Team", "General Sales Team")`
IF	Determines if expressions are true or false. Returns a given value if true and another value if false.	CRM Analytics and Data Cloud	`IF(logical_test, value_if_true, value_if_false)` logical_test is the condition you want to check. This expression must result in a value of either TRUE or FALSE. value_if_true is the value that is returned if the logical_test is true. value_if_false is the value that is returned if the logical_test is false.	`IF({NumberOfEmployees} > 1000, "Enterprise", "SMB")`
ISBLANK	Checks whether an expression is blank. If the expression is blank, TRUE is returned. If the expression contains a value, FALSE is returned.	CRM Analytics and Data Cloud	`IF(logical_test, value_if_true, value_if_false)` logical_test is the condition you want to check. This expression must result in a value of either TRUE or FALSE. value_if_true is the value that is returned if the logical_test is true. value_if_false is the value that is returned if the logical_test is false.	`IF(ISBLANK({Phone}), "Follow up required", "Contact info complete")`
ISNULL	Determines if an expression is null (blank) and returns TRUE if it’s blank. If it contains a value, the function returns FALSE.	CRM Analytics and Data Cloud	`IF(logical_test, value_if_true, value_if_false)` logical_test is the condition you want to check. This expression must result in a value of either TRUE or FALSE. value_if_true is the value that is returned if the logical_test is true. value_if_false is the value that is returned if the logical_test is false.	`IF(ISNULL({Website} ), "Website Not Set", "Website Available")`
NULLVALUE	Checks whether expression isn’t null and returns substitute expression. If expression is null, it returns the original expression value.	CRM Analytics and Data Cloud	`NULLVALUE(expression, substitute_expression)` expression is the number, date, or datetime field you want to check to see if it is empty (null). substitute_expression is the value you want the formula to return if the expression is empty.	`NULLVALUE({AnnualRevenue}, 0)`
OR	Determines if expressions are true or false. Returns TRUE if any expression is true. Returns FALSE if all expressions are false.	CRM Analytics and Data Cloud	`OR(logical1, logical1, ...)` logical1 is the first logical condition that you want to check. This expression must result in a value of either TRUE or FALSE. logical2 is the second logical condition that you want to check, which must also result in a value of TRUE or FALSE.	`OR({NumberOfEmployees} > 5000, {AnnualRevenue} < 10000)`

Additional Functions

Function Description Supported Runtime Syntax Example Syntax

COALESCE

Function	Description	Supported Runtime	Syntax	Example Syntax
COALESCE	Gets the first non-null value from a list of parameters, or replaces nulls with a different value. This function is often used to substitute a default value for null values when data is retrieved for display.	CRM Analytics and Data Cloud	`COALESCE(expr1, expr2, ...)` expression1 is the first column or text string to search for a non-null value. expression2 is the next column or text string to search for a non-null value.	`COALESCE({Phone}, {Fax})`
EXPLODE	Converts multivalue data into a new column where each element is a row. You can't nest the EXPLODE function within another function.	Data Cloud	`EXPLODE(field)` field is the field that contains the multivalue data you want to transform into individual rows.	`EXPLODE({Type})`
SEQUENCE	Returns an array of numbers or dates by defining the start and end values, and the interval between each value. If the interval isn't specified, the function returns all values between the start and end values.	Data Cloud	`SEQUENCE(start, end, step)` start is the beginning value of the series. end is the ending value of the series. step is the increment or interval between each value in the series.	`SEQUENCE({CreatedDate}, {LastModifiedDate}, 1)`

Gets the first non-null value from a list of parameters, or replaces nulls with a different value.

This function is often used to substitute a default value for null values when data is retrieved for display.

CRM Analytics and Data Cloud

COALESCE(expr1, expr2, ...)

expression1 is the first column or text string to search for a non-null value.
expression2 is the next column or text string to search for a non-null value.

COALESCE({Phone}, {Fax})

EXPLODE

Converts multivalue data into a new column where each element is a row. You can't nest the EXPLODE function within another function.

Data Cloud

EXPLODE(field)

field is the field that contains the multivalue data you want to transform into individual rows.

EXPLODE({Type})

SEQUENCE

Returns an array of numbers or dates by defining the start and end values, and the interval between each value. If the interval isn't specified, the function returns all values between the start and end values.

Data Cloud

SEQUENCE(start, end, step)

start is the beginning value of the series.
end is the ending value of the series.
step is the increment or interval between each value in the series.

SEQUENCE({CreatedDate}, {LastModifiedDate}, 1)

Compute Relative Transformation

To compute relative transformation, turn on Use the compute relative transformation in the formula node.

Use these functions when you create formulas to compute relative transformation.

Note A formula can include only one Compute Relative function.

Function	Description	Supported Runtime	Syntax	Example Syntax
ARRAYJOIN	Converts multivalue data into a string with elements separated by the specified delimiter.	CRM Analytics and Data Cloud	`ARRAYJOIN(field)` field is the multivalue field to be converted to a string.	`ARRAYJOIN({Type})`
COLLECTLIST	Returns multivalue data containing duplicates.	CRM Analytics and Data Cloud	`COLLECTLIST(field)` field is the multivalue field whose values you want to collect into an array. The function will take the value from this field for each record in the defined group.	`COLLECTLIST({Type})`
COLLECTSET	Returns multivalue data without duplicates.	Data Cloud	`COLLECTSET(field)` field is the multivalue field whose values you want to collect into a unique array. The function will only store each distinct value from this field once.	`COLLECTSET({Type})`
CURRENT	Returns the value of a specified field from the current row in a partition.	CRM Analytics and Data Cloud	`CURRENT(field)` field is the field whose value you want to retrieve for the current row.	`CURRENT ({AnnualRevenue})`
DENSERANK	Returns the dense rank of the current row in a partition.	CRM Analytics and Data Cloud	`DENSERANK()`	`DENSERANK()`
FIRSTVALUE	Returns the first value of a specified field in a partition.	CRM Analytics and Data Cloud	`FIRSTVALUE(field)` field is the field whose value you want to retrieve from the first record in the partition.	`FIRSTVALUE({Name})`
LAG	Returns the value of a specified field from the previous row in a partition.	CRM Analytics and Data Cloud	`LAG(field)` field is the field whose value you want to retrieve from a previous record.	`LAG({AnnualRevenue} )`
LASTVALUE	Returns the last value of a specified field in a partition.	CRM Analytics and Data Cloud	`LASTVALUE(field)` field is the field whose value you want to retrieve from the last record in the partition.	`LASTVALUE({Name})`
LEAD	Returns the value of a specified field from the next row in a partition.	CRM Analytics and Data Cloud	`LEAD(field)` field is the field whose value you want to retrieve from a subsequent record.	`LEAD({AnnualRevenue})`
RANK	Returns the rank of the current row in a partition.	CRM Analytics and Data Cloud	`RANK()`	`RANK()`
ROWNUMBER	Returns the serial number of the current row in a partition.	CRM Analytics and Data Cloud	`ROWNUMBER()`	`ROWNUMBER()`
SUM	Returns the cumulative sum of all the rows in a partition.	CRM Analytics and Data Cloud	`SUM(field)` field is the number field whose values you want to add together.	`SUM({AnnualRevenue})`

When the compute relative transformation option is enabled, the result of a formula node includes all the source fields and formula fields that you create.

Example A company wants to partition accounts by the parent account and order the partitioned data by the created date. The company also wants to know the difference between the previous annual revenue and the current annual revenue of each account. So the company uses two Compute Relative functions to partition Account records by the ParentId field and order the partitioned records by the CreatedDate field. To find the previous annual revenue, the company uses LAG({AnnualRevenue}). To find the current annual revenue, the company uses CURRENT({AnnualRevenue}). To calculate the difference, the company uses these two formulas in another formula node.

Configure End of Month Logic in Formula Nodes
When adding or subtracting months in DPE formula nodes, standard date calculations may not accurately land on the last day of the target month. This occurs because month lengths vary, and leap years affect February. For example, subtracting one month from April 30th (the end of the month) typically results in March 30th, which is not the last day of March. However, your business rule might require the date to be March 31st (or February 29th/28th if applicable).

Did this article solve your issue?

Let us know so we can improve!

Formulas

Required Editions

Math Functions

String Functions

Date Functions

Logical Functions

Additional Functions

Compute Relative Transformation

General Information

Required Cookies

Functional Cookies

Advertising Cookies

General Information

Required Cookies

Functional Cookies

Advertising Cookies

Cookie List

Product Area

Feature Impact

Edition

Experience

Formulas

Required Editions

Math Functions

String Functions

Date Functions

Logical Functions

Additional Functions

Compute Relative Transformation