Working with calculated fields
Starting with NexJ CRM version 23.05, in addition to regular fields and collection fields, you can now include calculated fields in a report. These fields are identified by the Calculated Field icon 
- references to existing report fields
- common arithmetic and comparison operators
- functions that perform calculations and manipulations on numeric, text, date, timestamp, currency, and Boolean data
- aggregate functions, which are functions where multiple values of one attribute are processed together to return a single result
- conditional logic and logical operators
To include a calculated field in the report, click Add Calculated Field below the Fields section on the Detail tab, and use the Add Calculated Field dialog to specify the new field name and the formula that should be used to calculate its value.
The dialog contains the following fields and sections:
| Field or Section | Description | Required |
|---|---|---|
| Field Name | The name of the calculated field, which is displayed in the report. | Yes |
| Field Description | The description of the calculated field. | No |
| Field Type | The field type or format is determined based on the results of the formula. It is not displayed as you are defining your formula. Click Validate after completing the formula to display the field type. The type is displayed until you start editing the formula again. | n/a |
| Formula | The formula you are using to define the calculated field. You can type directly into the field, or use the operator buttons, and field and function lists below to specify the formula. | n/a |
| Operators | Arithmetic, comparison, and logical operators which can be used in the formula. | n/a |
| Fields | Refer to any existing field included in the selected report subject area. Use the search field above the list to find a specific field without scrolling. Hover over the name of the field to display available actions.
| n/a |
| Functions | Select a supported function to be included in the formula. The list of functions is grouped by function type. Use the search field above the list to find a specific function without scrolling. Hover over the name of the function to display available actions.
| n/a |
Within the Formula field, the order of operations is as follows:
- Grouping or parentheses()
- Exponent ^
- Division and multiplication / *
- Addition and subtraction + -
- Equality and comparison = != < <= > >=
- Logical NOT
- Logical AND
- Logical OR
Arithmetic operators
| Operator | Description | Example | Input and return types |
|---|---|---|---|
| value1 + value2 | Addition. Adds two values together. | 'Open Days' + 7
| value1, value2: Numeric, date, timestamp Return: Return type matches input type |
| value1 - value2 | Subtraction. Subtracts the second value from the first value. | 'Open Days' - 7 | value1, value2: Numeric, date, timestamp Return: Return type matches input type |
| value1 * value2 | Multiplication. Multiplies two values together. | 'Sales Days' * 2 | value1, value2: Numeric, Return: Numeric |
| value1 / value2 | Division. Divides the first value by the second value. | 'Sales Days' / 2 | value1, value2: Numeric Return: Numeric |
| value1 ^ value2 | Exponentiation. Raises the first value to the power of the second value. | 'Sales Days' ^ 2 | value1, value2: Numeric Return: Numeric |
Comparison functions
The null, true, and false literals can be used with comparison functions. For example: 'Expected Amount' = null or 'Is Recurring' = false.
| Function | Description | Example | Input and return types |
|---|---|---|---|
| value1 = value2 | Equal. Returns TRUE if the two values are equal, FALSE otherwise. | 'Close Year' = 2020 | value1: String, Boolean, date, timestamp, numeric value2: The same as value1 Return: Boolean |
| value1 != value2 | Not Equal. Returns TRUE if the two values are unequal, FALSE otherwise. | 'Close Year' != 2020 | value1: String, Boolean, date, timestamp, numeric value2: The same as value1 Return: Boolean |
| value1 > value2 | Greater Than. Returns TRUE if value1 is greater than value2, FALSE otherwise. | 'Close Year' > 2020 | value1: Numeric, date, timestamp value2: The same as value1 Return: Boolean |
| value1 >= value2 | Greater Than or Equal. Returns TRUE if value1 is greater than or equal to value2, FALSE otherwise. | 'Close Year' >= 2020 | value1: Numeric, date, timestamp value2: The same as value1 Return: Boolean |
| value1 < value2 | Less Than. Returns TRUE if value1 is less than value2, FALSE otherwise. | 'Close Year' < 2020 | value1: Numeric, date, timestamp value2: The same as value1 Return: Boolean |
| value1 <= value2 | Less Than or Equal To. Returns TRUE if value1 is less than or equal to value2, FALSE otherwise. | 'Close Year' <= 2020 | value1: Numeric, date, timestamp value2: The same as value1 Return: Boolean |
Logical functions
| Function | Description | Example | Input and return types |
|---|---|---|---|
( ) | Parentheses. Used to define the order of evaluation. | value1 AND (value2 OR value3) NOT (value1 OR value2) | n/a |
| value1 AND value2 | Logical AND. Returns TRUE if both arguments value1 and value2 are TRUE. | 'Is Recurring' AND 'Is All Day Event' | value1: Boolean value2: Boolean Return: Boolean |
| value1 OR value2 | Logical OR. Returns TRUE if at least one of arguments value1 and value2 is TRUE. | 'Is Recurring' OR 'Is All Day Event' | value1: Boolean value2: Boolean Return: Boolean |
| NOT value | Logical NOT. Returns TRUE if the argument value is FALSE. Returns FALSE if the argument value is TRUE. | NOT 'Is All Day Event' | value: Boolean Return: Boolean |
Conditional logic functions
The null, true and false literals can be used with conditional logic functions. For example: IF('Expected Amount' > 400000, "Flagged", null) or IF('Expected Amount' > 400000, true, false).
| Function | Description | Example | Input and return types |
|---|---|---|---|
| IF(condition, value1, value2) | Returns value1 if condition is TRUE and value2 otherwise. The data types of value1 and value2 must be the same. | IF(DATEDIFF("day", 'Expected Close', 'Actual Close') >= 0, "On time", "Late") | condition: Boolean value1: Boolean, string, numeric, date, timestamp value2: The same as value1 Return: Any format, either value1 or value2 |
| ISNULL(value) | Returns TRUE if value is NULL and FALSE otherwise. | ISNULL('Company : Coverage Parent') | value: Any Return: Boolean |
Numeric functions
| Function | Description | Example | Input and return types |
|---|---|---|---|
| ABS(number) | Returns absolute value of the number. | ABS('Actual Amount'-'Expected Amount') | number: Numeric Return: Numeric |
| CEILING(number) | Rounds the number up to the nearest integer. | CEILING('Actual Amount') | number: Numeric Return: Numeric (integer) |
| FLOOR(number) | Rounds the number down to the nearest integer. | FLOOR('Actual Amount') | number: Numeric Return: Numeric (integer) |
| ROUND(number, precisionNumber) | Rounds the number to the precisionNumber of digits. | ROUND('List Price',2) | number: Numeric precisionNumber: Numeric (integer) Return: Numeric |
| SQRT(number) | Calculates the square root of the specified positive number. | SQRT(25) | number: Numeric Return: Numeric |
String functions
| Function | Description | Example | Input and return types |
|---|---|---|---|
| CONCAT(text1, text2, ...) | Combines multiple text values into a single text. | CONCAT("Dear ",'Full Name') | text1, text2, ...: String Return: String |
| CONTAINS(text, searchText) | Returns TRUE if text contains searchText, FALSE otherwise. | CONTAINS('Full Name', "Lamont") | text: String searchText: String Return: Boolean |
| LEFT(text, number) | Returns the specified number of leftmost characters from the text. | LEFT('Home Address : Postal Code/Zip', 3) | text: String number: Numeric (integer) Return: String |
| LENGTH(text) | Returns the number of characters in the specified text. | LENGTH('Description') | text: String Return: Numeric (integer) |
| LOWER(text) | Coverts all letters in the specified text to lower case. | LOWER('Home Address : Postal Code/Zip') | text: String Return: String |
| RIGHT(text, number) | Returns the specified number of rightmost characters from the text. | RIGHT(TEXT('Start Time'), 2) | text: String number: Numeric (integer) Return: String |
| TEXT(value) | Converts a number, currency, Boolean, date, or timestamp value to a text. | TEXT('Date of Birth') | value: Numeric, currency, Boolean, date, timestamp Return: String |
| TRIM(text) | Returns the specified text with leading and trailing spaces removed. | TRIM('Description') | text: String Return: String |
| UPPER(text) | Converts all letters in the specified text to upper case. | UPPER('Home Address : Postal Code/Zip') | text: String Return: String |
Date and timestamp functions
| Function | Description | Example | Input and return types |
|---|---|---|---|
| DATE(text) | Converts text in the YYYY-MM-DD format to the equivalent date value. | DATE("2022-11-23") | text: String Return: Date |
| DATEADD(interval, number, value) | Adds the specified number of intervals to a date or timestamp value. Use a negative number to subtract the intervals instead. Supported intervals: "year", "month", "week", "day", "hour", "minute", "second", "millisecond". | DATEADD("month", 1, 'Expected Close Date') | interval: String number: Numeric (integer) value: Date, timestamp Return: Date, timestamp |
| DATEDIFF(interval, value1, value2) | Calculates the difference between the two date or timestamp values as the number of elapsed intervals, by subtracting value2 from value1. Supported intervals: "day", "hour", "minute", "second", "millisecond". | DATEDIFF("minute", 'End Time', 'Start Time') | interval: String value1: Date, timestamp value2: Date, timestamp Return: Numeric (integer) |
| DAY(value) | Returns the day of the specified date or timestamp value as a number. | DAY(TODAY()) | value: Date, timestamp Return: Numeric (integer) |
| HOUR(value) | Returns the hour of the specified timestamp value as a number. For date values, zero is returned. | HOUR(NOW()) | value: Date, timestamp Return: Numeric (integer) |
| MINUTE(value) | Returns the minute of the specified timestamp value as a number. For date values, zero is returned. | MINUTE(NOW()) | value: Date, timestamp Return: Numeric (integer) |
| MONTH(value) | Returns the month of the specified date or timestamp value as a number. | MONTH(TODAY()) | value: Date, timestamp Return: Numeric (integer) |
| NOW() | Returns current date and time as a timestamp value. | NOW() | Return: Timestamp |
| QUARTER(value) | Returns the calendar quarter of the specified date or timestamp value as a number. | QUARTER(TODAY()) | value: Date, timestamp Return: Numeric (integer) |
| SECOND(value) | Returns the second of the specified timestamp value as a number. For date values, zero is returned. | SECOND(NOW()) | value: Date, timestamp Return: Numeric (integer) |
| TIMESTAMP(text) | Converts text in the YYYY-MM-DD HH:mm:ss.SSS format to the equivalent timestamp value. | TIMESTAMP("2022-11-23 17:12:33.147") | text: String Return: Timestamp |
| TODAY() | Return today's date as a date value. | TODAY() | Return: Date |
| YEAR(value) | Returns the year of the specified date or timestamp value as a number. | YEAR(TODAY()) | value: Date, timestamp Return: Numeric (integer) |
Currency functions
| Function | Description | Example | Input and return types |
|---|---|---|---|
| CURRENCY(number) | Formats the specified number as a currency value, using the report currency units. | CURRENCY('Expected Amount' * 0.15) | number: Numeric Return: Currency |
Aggregate functions
| Function | Description | Example | Input and return types |
|---|---|---|---|
| AVG(collectionField) | Returns the average value of the specified field in the collection. | AVG('Related Opportunities : Actual Amount') | collectionField: Numeric Return: Numeric |
| COUNT(collectionField) | Returns the number of records in the specified collection field. | COUNT('Assign To') | collectionField: Any Return: Numeric |
| MAX(collectionField) | Returns the largest value of the specified field in the collection | MAX('Activities : Start Time') | collectionField: Numeric, date, timestamp Return: Numeric, date, timestamp, same as collectionField |
| MIN(collectionField) | Returns the smallest value of the specified field in the collection. | MIN('Activities : Start Time') | collectionField: Numeric, date, timestamp Return: Numeric, date, timestamp, same as collectionField |
| PERCENTOF(number, [groupingField]) | Returns number as a percentage of sum of all values within each grouping. If optional groupingField is omitted, the sum of all values included in the report is used.
Fields containing this function cannot be included in a report with aggregate calculated field filters.
| PERCENTOF('Expected Amount', 'Stage') | number: Numeric groupingField: Any Return: Numeric, formatted as a decimal value between 0 and 1 |
| RANK(number, [sortOrder, [groupingField]]) | Returns the rank within each grouping. If optional sortOrder is omitted, then the sort order defaults to descending. If optional groupingField is omitted, the rank within all values included in the report is returned. groupingField cannot be specified if sortOrder is omitted. | RANK('Expected Amount', "desc", 'Close Year') | number: Numeric sortOrder supported values: groupingField: Any Return: Numeric |
| SUM(collectionField) | Returns the sum of the values of the specified field in the collection. | SUM('Related Opportunities : Actual Amount') | collectionField: Numeric Return: Numeric |
