--- schema_version: "1" id: b20509d6492e8e7a1bdfafd0cd1448d3626623f5a9efce7ece3d812029d9aa9d title: Formulas collection: admin version: current locale: en-us portal_url: https://docs.salesforce.com/admin/current/en-us/ind/264-0-0/dpe_formula.md canonical_url: https://help.salesforce.com/s/articleView?id=ind.dpe_formula.htm&release=264.0.0&type=5 source_site: help.salesforce.com content_type: documentation content_hash: cd6416c679d97b48156d3b4fbc7dde05787bdc8f6cfb52492b45438672d3eae5 description: 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. Order the formulas within a node so that a formula can use the result of an earlier formula. Use the compute relative transformation option to determine the trends of specific fields. products: - Cross Cloud release: "264" --- # 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. Order the formulas within a node so that a formula can use the result of an earlier formula. 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](https://help.salesforce.com/s/articleView?id=ind.dpe_define_input_variables.htm&release=264.0.0&type=5 "Define flexible data processing rules by using input variables to define default values, filter records, and create formulas. To dynamically control the data filters and formulas at run time, create variables without values and use them to configure Filter and Formula nodes. During a definition run, specify variable values based on your specific requirements. For example, to filter opportunities above a certain value for different fiscal years, set a default value for the input variable and then update it when you run the definition.") 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](https://sf-zdocs-cdn-prod.zoominsoftware.com/tdta-ind-data_processing_engine-264-0-0-production-enus/80e06924-e6c0-4b82-81cf-e99ccd77d857/images/icon_note.png) 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. ## Order Formulas and Reference an Earlier Formula Create multiple formulas in a single formula node. Each formula has a sequence number that sets the order in which the formulas run. To change the order, drag a formula by its drag handle or use the keyboard. To reorder a formula with the keyboard, press **Space** or **Enter** to pick up the formula, use the arrow keys to move it up or down, and press **Space** or **Enter** to drop it. To cancel, press **Escape**. A formula can reference the output of any formula that appears above it in the sequence. In the expression editor, the outputs of earlier formulas appear under Formula Fields, along with Data Source Fields and Input Variable Fields. A formula can't reference another formula at the same position or later in the sequence. For example, to calculate the total price, create these formulas in this order in a single formula node: - NetPrice, with the expression `ListPrice - Discount`. - Tax, with the expression `NetPrice * TaxRate`. NetPrice is available under Formula Fields because it's earlier in the sequence. - TotalPrice, with the expression `NetPrice + Tax`. ![Formula node](https://sf-zdocs-cdn-prod.zoominsoftware.com/tdta-ind-data_processing_engine-264-0-0-production-enus/80e06924-e6c0-4b82-81cf-e99ccd77d857/data_processing_engine/images/data_processing_engine_formula.png) Order your formulas so that dependencies flow from top to bottom. Add base calculations first, and add the formulas that depend on them later. If you delete or rename an earlier formula that a later formula references, update the later formula before you save. ![Example](https://sf-zdocs-cdn-prod.zoominsoftware.com/tdta-ind-data_processing_engine-264-0-0-production-enus/80e06924-e6c0-4b82-81cf-e99ccd77d857/images/icon_example.png) Example To calculate the average annual revenue, the formula is {AnnualRevenue}/12. The formula field is of type Number. [Math Functions](https://help.salesforce.com/s/articleView?id=ind.dpe_formula.htm&release=264.0.0&type=5) | [String Functions](https://help.salesforce.com/s/articleView?id=ind.dpe_formula.htm&release=264.0.0&type=5) | [Date Functions](https://help.salesforce.com/s/articleView?id=ind.dpe_formula.htm&release=264.0.0&type=5) | [Logical Functions](https://help.salesforce.com/s/articleView?id=ind.dpe_formula.htm&release=264.0.0&type=5) | [Additional Functions](https://help.salesforce.com/s/articleView?id=ind.dpe_formula.htm&release=264.0.0&type=5) | [Compute Relative Transformation](https://help.salesforce.com/s/articleView?id=ind.dpe_formula.htm&release=264.0.0&type=5) ## 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 360

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 360

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 360

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 360

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 360

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 360

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 360

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 360

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 360

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 360

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 360

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 360

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 360

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 360

CONTAINS(string, searchString)

  • 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 360

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 360

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 360

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 360

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.

Restriction The MD5 function isn't available in Government Cloud. Use SHA256 instead.
 Data 360

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 360

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 360

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 360

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 360

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 360

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 360

UPPER(string)

string is the text or field to convert to uppercase.

 UPPER({BillingPostalCode})
UUID Returns a newly generated unique ID. CRM Analytics and Data 360 UUID() UUID()
VALUE Converts a text string that represents a number to a value that represents the same number. CRM Analytics and Data 360

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 360

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 360

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 360

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 360

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 360

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 360

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 360

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 360 NOW() (NOW() - {CreatedDate}) * 24
TODAY Returns the current date as a date data type. CRM Analytics and Data 360 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 360

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 360

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 360

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 360

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 360

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 360

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 360

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 360

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 360

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

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 360

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 360

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 360

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. ![Compute Relative Transformation option](https://sf-zdocs-cdn-prod.zoominsoftware.com/tdta-ind-data_processing_engine-264-0-0-production-enus/80e06924-e6c0-4b82-81cf-e99ccd77d857/data_processing_engine/images/data_processing_engine_compute_relative_option.png) Use these functions when you create formulas to compute relative transformation. ![Note](https://sf-zdocs-cdn-prod.zoominsoftware.com/tdta-ind-data_processing_engine-264-0-0-production-enus/80e06924-e6c0-4b82-81cf-e99ccd77d857/images/icon_note.png) 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 360

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 360

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 360

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 360

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 360 DENSERANK() DENSERANK()
FIRSTVALUE Returns the first value of a specified field in a partition. CRM Analytics and Data 360

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 360

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 360

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 360

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 360 RANK() RANK()
ROWNUMBER Returns the serial number of the current row in a partition. CRM Analytics and Data 360 ROWNUMBER() ROWNUMBER()
SUM Returns the cumulative sum of all the rows in a partition. CRM Analytics and Data 360

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. ![Compute Relative Transformation](https://sf-zdocs-cdn-prod.zoominsoftware.com/tdta-ind-data_processing_engine-264-0-0-production-enus/80e06924-e6c0-4b82-81cf-e99ccd77d857/data_processing_engine/images/data_processing_engine_compute_relative.png) ![Example](https://sf-zdocs-cdn-prod.zoominsoftware.com/tdta-ind-data_processing_engine-264-0-0-production-enus/80e06924-e6c0-4b82-81cf-e99ccd77d857/images/icon_example.png) 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](https://help.salesforce.com/s/articleView?id=ind.dpe_configure_end_of_month_logic_in_formula_nodes.htm&release=264.0.0&type=5)** 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).