Working with calculated fields | NexJ Documentation

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 . They allow you to specify a formula to create a temporary field that can be used in your report. The field can contain a combination of:

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. Select the information button to find out more about the field, including the field type. Select the plus button to include the field in the formula.	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. Select the information button to find out more about the function, including an example and the return field type. Select the plus button to include the function in the formula.	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

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

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: `desc`, `asc` 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