---
title: "Calculated Fields - Supported Functions"
canonical: "https://support.appfire.com/space/ART/1578500991/Calculated%20Fields%20-%20Supported%20Functions"
format: markdown
---
> Macro (aura-html)

The below table explains in detail the functions that can be used to create calculated fields. The functions are divided into 4 categories

> ℹ️ Numeric results in reports, including fractional values returned by functions such as *DIVIDE*, *DAYS*, and *BUSINESS DAYS*, are now shown with up to 4 decimal places instead of 2. The worked examples below still show the older 2-decimal display for illustration; the underlying calculation is unchanged, only the displayed precision is different.

## **Numeric Functions**

For parameters required by the functions,

**Is Mandatory?**: Indicates whether the parameter is mandatory or not. If the value is mandatory and a value isn't provided for the parameter, the add-on will throw an error

**Is Variable Length?**: The value 'No' indicates that parameter takes single value. Variable length indicates, the parameter can take multiple values. You can use "Add parameter" link to add more values

| **Function **  
**Name & Description** | **Return Type** | **Examples** | **Parameters** |
| --- | --- | --- | --- |
| **Name:  Description** | **Type** | **Is Mandatory?** | **Is Variable Length?** |
| **ADD **- Returns the sum of all the numbers given as parameters | Numeric | ADD(8, 101.4) = 109.4  
ADD(8, 101.4, 10, 20) = 139.4  
ADD(NULL, 10, 20, NULL) = 30 | value1:  Initial Value | Numeric | Yes | No |
| value2:  Value to be added | Numeric | Yes | Yes |
| **SUBTRACT **- Returns the difference between provided numbers | Numeric | SUBTRACT(101.4, 1.4) = 100  
SUBTRACT(101.4, -8) = 109.4  
SUBTRACT(NULL, 10) = -10 | value1:  Initial value | Numeric | Yes | No |
| value2:  Value to be subtracted | Numeric | Yes | No |
| **MULTIPLY** - Returns the product of all the numbers given as parameters | Numeric | MULTIPLY(10, 1.1) = 11  
MULTIPLY(NULL, 10) = NULL  
MULTIPLY(10, 10, 10, 1.1234) = 1123.4 | value:  Initial value | Numeric | Yes | No |
| multiplier:  Value to be multiplied | Numeric | Yes | Yes |
| **DIVIDE - **Returns the quotient after a number is divided by a divisor | Numeric | DIVIDE(15, 3) = 5,  
DIVIDE(15, 4) = 3.75  
DIVIDE(100, 1000) = 0.1  
DIVIDE(0, 4) = 0  
DIVIDE(15, 0) = NULL  
DIVIDE(NULL, 4) = NULL  
DIVIDE(15, NULL) = NULL | value:  Initial value | Numeric | Yes | No |
| divisor:  Value to divide by | Numeric | Yes | No |
| **MOD** - Returns the remainder after a number is divided by a divisor | Numeric | MOD(15, 3) = 0  
MOD(15, 4) = 3  
MOD(100, 1000) = 100  
MOD(10, 0.5) = 0  
MOD(0, 4) = 0  
MOD(15, 0) = NULL  
MOD(NULL, 4) = NULL  
MOD(15, NULL) = NULL | value:  Initial value | Numeric | Yes | No |
| divisor:  Value to divide the initial value by | Numeric | Yes | No |
| **ROUND **- Returns the value of number rounded to the nearest Integer | Numeric | ROUND(100) = 100  
ROUND(100.495) = 100  
ROUND(100.5) = 101  
ROUND(100.998) = 101  
ROUND(-100.495) = -100  
ROUND(-100.5) = -100  
ROUND(-100.998) = -101  
ROUND(NULL) = NULL | value:  Initial value | Numeric | Yes | No |
| **FLOOR** - Returns the largest Integer value that is less than or equal to the parameter | Numeric | FLOOR(10) = 10  
FLOOR(10.8) = 10  
FLOOR(-10.8) = -11  
FLOOR(NULL) = NULL | value:  Initial value | Numeric | Yes | No |
| **CEILING **- Returns the smallest Integer value that is greater than or equal to the parameter | Numeric | CEILING(10) = 10  
CEILING(10.8) = 11  
CEILING(-10.8) = -10  
CEILING(NULL) = NULL | value:  Initial value | Numeric | Yes | No |
| **ABS** - Returns the absolute value of the parameter | Numeric | ABS(10.1) = 10.1  
ABS(-10.1) = 10.1  
ABS(0) = 0  
ABS(NULL) = NULL | value:  Initial value | Numeric | Yes | No |
| **TO NUMBER** - Returns Number value represented by the input string | Numeric | TO NUMBER('-10.1') = -10.1  
TO NUMBER('10.100') = 10.1  
TO NUMBER('10.1.1') = NULL  
TO NUMBER('1,100.1') = NULL  
TO NUMBER('10.1a') = NULL  
TO NUMBER('invalid') = NULL  
TO NUMBER(NULL) = NULL | value:  Initial value | Numeric | Yes | No |
| **LESS THAN **- Returns TRUE if value1 is less than value2. Otherwise returns FALSE | Boolean | LESS THAN(10.0, 10) = FALSE  
LESS THAN(10.0, 9.9) = FALSE  
LESS THAN(10.0, 10.01) = TRUE  
LESS THAN(-10.0, -9.9) = TRUE  
LESS THAN(-10.0, -10.01) = FALSE  
LESS THAN(NULL, 10) = NULL | value1:  Initial value | Numeric | Yes | No |
| value2:  Value to be compared against | Numeric | Yes | No |
| **LESS THAN OR EQUALS** - Returns TRUE if value1 is less than or equal to value2. Otherwise returns FALSE | Boolean | LESS THAN OR EQUALS(10.0, 10) = TRUE  
LESS THAN OR EQUALS(10.0, 9.9) = FALSE  
LESS THAN OR EQUALS(10.0, 10.01) = TRUE  
LESS THAN OR EQUALS(NULL, 10) = NULL | value1:  Initial value | Numeric | Yes | No |
| value2:  Value to be compared against | Numeric | Yes | No |
| **GREATER THAN** - Returns TRUE if value1 is greater than value2. Otherwise returns FALSE | Boolean | GREATER THAN(10.0, 10) = FALSE  
GREATER THAN(10.0, 9.9) = TRUE  
GREATER THAN(10.0, 10.01) = FALSE  
GREATER THAN(-10.0, -9.9) = FALSE  
GREATER THAN(-10.0, -10.01) = TRUE  
GREATER THAN(NULL, 10) = NULL | value1:  Initial value | Numeric | Yes | No |
| value2:  Value to be compared against | Numeric | Yes | No |
| **GREATER THAN OR EQUALS **- Returns TRUE if value1 is greater than or equal to value2. Otherwise returns FALSE | Boolean | GREATER THAN OR EQUALS(10.0, 10) = TRUE  
GREATER THAN OR EQUALS(10.0, 9.9) = TRUE  
GREATER THAN OR EQUALS(10.0, 10.01) = FALSE  
GREATER THAN OR EQUALS(NULL, 10) = NULL | value1:  Initial value | Numeric | Yes | No |
| value2:  Value to be compared against | Numeric | Yes | No |


## **String Functions**

For parameters required by the functions,

**Is Mandatory?**: Indicates whether the parameter is mandatory or not. If the value is mandatory and a value isn't provided for the parameter, the add-on will throw an error

**Is Variable Length?**: The value 'No' indicates that parameter takes single value. Variable length indicates, the parameter can take multiple values. You can use "Add parameter" link to add more values

| **Function Name & Description** | **Return Type** | **Examples** | **Parameters** |
| --- | --- | --- | --- |
| **Name: Description** | **Type** | **Is Mandatory?** | **Is Variable Length?** |
| **CONCAT** - Concatenates the provided string values into a single string | String | CONCAT('Join', ' ', 'these', ' ', 'words') = 'Join these words'  
CONCAT('Join', NULL, 'these', NULL, 'words') = 'Jointhesewords'  
CONCAT(NULL, NULL) = '' | value1:  Initial value | String | Yes | No |
| value2:  Value to be concatenated to the initial value | String | Yes | Yes |
| **SUBSTRING **- Returns the substring of the value. Substring starting at startPos, till endPos (if provided, otherwise, till end of the value) is returned.  
**Note**: The position index starts with zero. Value of 0, will point to the first character on the left.<br>If negative values are used for position index, then index calculation is done from right. Value of -1, will point to the first character on the right. | String | SUBSTRING('The fox jumped over the fence', 0) = 'The fox jumped over the fence'<br>SUBSTRING('The fox jumped over the fence', 0, 10) = 'The fox ju'<br>SUBSTRING('The fox jumped over the fence', 10, 20) = 'mped over '<br>SUBSTRING('The fox jumped over the fence', 10, 100) = 'mped over the fence'<br>SUBSTRING('The fox jumped over the fence', -10) = ' the fence'<br>SUBSTRING('The fox jumped over the fence', -10, 25) = ' the f'<br>SUBSTRING('The fox jumped over the fence', 0, -10) = 'The fox jumped over'<br>SUBSTRING('The fox jumped over the fence', 30, 40) = ''<br>SUBSTRING(NULL, 30, 40) = NULL | value:  Initial value | String | Yes | No |
| startPos: Character position which will be start of extracted substring | Numeric | Yes | No |
| endPos:  Character position till which the substring will be extracted.  
If not provided, then substring till the end of the initial value will be extracted | Numeric | No | No |
| **UPPER** - Returns the value with all alphabets converted to Upper Case | String | UPPER('this is all lower') = 'THIS IS ALL LOWER'  
UPPER('tHis IS MixEd Case') = 'THIS IS MIXED CASE'  
UPPER('') = ''  
UPPER(NULL) = NULL | value:  Initial value | String | Yes | No |
| **LOWER** - Returns the value with all alphabets converted to Lower Case | String | LOWER('THIS IS ALL UPPER') = 'this is all upper'  
LOWER('tHis IS MixEd Case') = 'this is mixed case'  
LOWER('') = ''  
LOWER(NULL) = NULL | value:  Initial value | String | Yes | No |
| **TO STRING** - Returns the String value of passed parameter. Useful for converting Numeric, Date or Boolean value to String.  
**Note**: Parameter of type ANY indicates, that function can take any argument type (String, Numeric, Date OR Boolean). | String | TO STRING(100.01) = '100.01'  
TO STRING(TRUE) = 'true'  
TO STRING(FALSE) = 'false'  
TO STRING(NULL) = NULL  
TO STRING('This is a string value') = 'This is a string value' | value:  Initial value | ANY | Yes | No |
| **LENGTH **- Returns the length of provided string value | Numeric | LENGTH('This is a string value') = 22  
LENGTH('') = 0  
LENGTH(NULL) = 0 | value:  Initial value | String | Yes | No |
| **POSITION **- Returns the position of first occurrence of searchString in the value. Return value of -1, indicates that searchString was not found in the initial value.  
If the startPos is provided, then search will be started from startPos, otherwise search will be done from start of the value.  
**Note**: The position index starts with zero. Value of 0, will point to the first character on the left.<br>If negative values are used for position index, then index calculation is done from right. Value of -1, will point to the first character on the right. | Numeric | POSITION('This is a string value', 'This') = 0  
POSITION('This is a string value', 'This', 1) = -1  
POSITION('This is a string value', 'value', -10) = 17  
POSITION('This is a string value', 'is') = 2  
POSITION('This is a string value', 'is', NULL) = 2  
POSITION('This is a string value', 'numeric') = -1  
POSITION('This is a string value', NULL) = -1  
POSITION(NULL, 'This is a string value') = -1 | value:  Initial value | String | Yes | No |
| searchString:  String to be searched in the initial value | String | Yes | No |
| startPos:  Character position from which search will be performed.  
If not provided, the search will be performed from the starting of the initial value. | Numeric | No | No |
| **CONTAINS** - Returns TRUE if the searchString is contained in the value. Otherwise returns FALSE | Boolean | CONTAINS('This is a string value', 'is') = TRUE  
CONTAINS('This is a string value', 'numeric') = FALSE  
CONTAINS('This is a string value', NULL) = FALSE  
CONTAINS(NULL, 'This is a string value') = FALSE | value:  Initial value | String | Yes | No |
| searchString:  String to be searched in the initial value | String | Yes | No |
| **IN** - Returns TRUE if the value is contained in the List. Otherwise returns FALSE | Boolean | IN('OPEN', 'TO DO', 'OPEN', 'IN PROGRESS') = TRUE  
IN('DONE', 'TO DO', 'OPEN', 'IN PROGRESS') = FALSE  
IN(NULL, 'TO DO', 'OPEN', 'IN PROGRESS') = FALSE  
IN('DONE', '', NULL) = FALSE | value:  Initial value | String | Yes | No |
| listItem:  List in which the initial value is be searched | String | Yes | Yes |

## **Date Functions**

For parameters required by the functions,

**Is Mandatory?**: Indicates whether the parameter is mandatory or not. If the value is mandatory and a value isn't provided for the parameter, the add-on will throw an error

**Is Variable Length?**: The value 'No' indicates that parameter takes single value. Variable length indicates, the parameter can take multiple values. You can use "Add parameter" link to add more values

- Date value is automatically converted to Logged-in users timezone. For ex: NOW()
- Date value displayed in the report, will have a default format (MMM dd, yyyy) applied. This format can be changed from the field settings in the Report page

> ℹ️ The *DAYS* and *BUSINESS DAYS* functions below return fractional day values. These are now displayed with up to 4 decimal places instead of 2; the examples below still show the older 2-decimal display for illustration.

| **Function Name & Description** | **Return Type** | **Examples** | **Parameters** |
| --- | --- | --- | --- |
| **Name:  Description** | **Type** | **Is Mandatory?** | **Is Variable Length?** |
| **FORMAT DATE** - Returns the formatted date as per selected Pattern | String | NOW() = '2019-04-11T12:02:53.854+0530'  
FORMAT DATE(NOW(), 'MMM dd, yyyy') = 'Apr 11, 2019'  
FORMAT DATE(NOW(), 'MM/dd/yy hh:mm:ss aaa') = '04/11/19 12:02:53 PM' | date:  Initial value | Date | Yes | No |
| pattern:  Pattern value to be used to format the date | String | Yes | No |
| **DAYS** - Returns the days between provided date values | Numeric | NOW() = '2019-04-11T14:02:25.000+0530'  
DAYS(NOW(), '2019-01-01T12:00:00.00+0530') = 100.09  
DAYS('2019-12-31T12:00:00.00+0530', NOW()) = 263.91  
DAYS(NOW(), '2019-12-31T12:00:00.00+0530') = -263.91  
DAYS(NOW(), NULL) = NULL<br>**Note**: For the examples the date value is shown without any format. Also see the precision note above: current output shows up to 4 decimal places rather than 2. | aDate:  Initial value | Date | Yes | No |
| bDate:  Number of days will be calculated between this value and the initial value | Date | Yes | No |
| **BUSINESS DAYS** - Returns the business days between provided date values.  
**Note**:  For calculating Business Days, days marked as weekend days in the "Page settings" in Report page are excluded | Numeric | NOW() = '2019-04-11T14:08:47.225+0530'  
Weekend Days = Saturday and Sunday  
BUSINESS DAYS(NOW(), '2019-01-01T12:00:00.00+0530') = 72.27  
BUSINESS DAYS(NOW(), '2019-12-31T12:00:00.00+0530') = -187.73  
BUSINESS DAYS('2019-12-31T12:00:00.00+0530', NOW()) = 187.73  
BUSINESS DAYS(NOW(), NULL) = NULL<br>**Note**: For the examples the date value is shown without any format. Also see the precision note above: current output shows up to 4 decimal places rather than 2. | aDate:  Initial value | Date | Yes | No |
| bDate:  Number of business days will be calculated between this value and the initial value | Date | Yes | No |
| **NOW** - Returns the current date and time value | Date | NOW() = '2019-04-11T12:02:53.854+0530'  
**Note**: For the examples the date value is shown without any format | None | N/A | N/A | N/A |
| **IS BEFORE** - Returns TRUE if the aDate is chronologically before bDate. Otherwise returns FALSE | Boolean | NOW() = '2019-04-11T12:02:53.854+0530'  
IS BEFORE(NOW(), '2019-04-11T12:00:00.000+0530') = FALSE  
IS BEFORE(NOW(), '2019-04-12T12:00:00.000+0530') = TRUE  
IS BEFORE(NOW(), NOW()) = FALSE  
**Note**: For the examples the date value is shown without any format | aDate:  Initial value | Date | Yes | No |
| bDate:  Date against which the initial value will be compared | Date | Yes | No |
| **IS AFTER** - Returns TRUE if the aDate is chronologically after bDate. Otherwise returns FALSE | Boolean | NOW() = '2019-04-11T12:02:53.854+0530'  
IS AFTER(NOW(), '2019-04-11T12:00:00.000+0530') = TRUE  
IS AFTER(NOW(), '2019-04-12T12:00:00.000+0530') = FALSE  
IS AFTER(NOW(), NOW()) = FALSE  
**Note**: For the examples the date value is shown without any format | aDate:  Initial value | Date | Yes | No |
| bDate:  Date against which the initial value will be compared | Date | Yes | No |

## **Logical Functions**

For parameters required by the functions,

**Is Mandatory?**: Indicates whether the parameter is mandatory or not. If the value is mandatory and a value isn't provided for the parameter, the add-on will throw an error

**Is Variable Length?**: The value 'No' indicates that parameter takes single value. Variable length indicates, the parameter can take multiple values. You can use "Add parameter" link to add more values

| **Function Name & Description** | **Return Type** | **Examples** | **Parameters** |
| --- | --- | --- | --- |
| **Name:  Description** | **Type** | **Is Mandatory?** | **Is Variable Length?** |
| **EQUALS **- Returns TRUE if value1 is equal to value2, otherwise returns FALSE | Boolean | EQUALS('Hello', 'hello') = FALSE  
EQUALS('Hello', 'Hello') = TRUE  
EQUALS(100, 100.0) = TRUE  
EQUALS(0, 0.00001) = FALSE  
EQUALS(NOW(), NOW()) = TRUE  
EQUALS(TRUE(), TRUE) = TRUE  
EQUALS(TRUE(), FALSE) = FALSE  
EQUALS(NULL, NULL) = NULL  
EQUALS(0, NULL) = NULL | value1:  Initial value | ANY | Yes | No |
| value2:  Value to be compared with the initial value | ANY | Yes | No |
| **NOT EQUALS** - Returns TRUE if value1 is not equal to value2, otherwise returns FALSE | Boolean | NOT EQUALS('Hello', 'hello') = TRUE  
NOT EQUALS('Hello', 'Hello') = FALSE  
NOT EQUALS(100, 100.0) = FALSE  
NOT EQUALS(0, 0.00001) = TRUE  
NOT EQUALS(NOW(), NOW()) = FALSE  
NOT EQUALS(TRUE(), TRUE) = FALSE  
NOT EQUALS(TRUE(), FALSE) = TRUE  
NOT EQUALS(NULL, NULL) = NULL  
NOT EQUALS(0, NULL) = NULL | value1:  Initial value | ANY | Yes | No |
| value2:  Value to be compared with the initial value | ANY | Yes | No |
| **AND** - Returns the value with ANDing of all parameters. Returns FALSE if any one the parameter is FALSE. Otherwise returns TRUE | Boolean | AND(TRUE, TRUE) = TRUE  
AND(TRUE, FALSE) = FALSE  
AND(FALSE, TRUE) = FALSE  
AND(FALSE, FALSE) = FALSE  
AND(TRUE, TRUE, NULL, TRUE, NULL, NULL) = TRUE  
AND(TRUE, TRUE, NULL, FALSE, NULL, NULL) = FALSE  
AND(NULL, NULL) = NULL | value1:  Initial value | Boolean | Yes | No |
| value2:  Value to be ANDed with the initial value | Boolean | Yes | Yes |
| **OR **- Returns the value with ORing of all parameters. Returns TRUE if any one of the parameter is TRUE. Otherwise returns FALSE | Boolean | OR(TRUE, TRUE) = TRUE  
OR(TRUE, FALSE) = TRUE  
OR(FALSE, TRUE) = TRUE  
OR(FALSE, FALSE) = FALSE  
OR(FALSE, FALSE, NULL, FALSE, NULL, NULL) = FALSE  
OR(FALSE, TRUE, NULL, FALSE, NULL, NULL) = TRUE  
OR(NULL, NULL) = NULL | value1:  Initial value | Boolean | Yes | No |
| value2:  Value to be ORed with the initial value | Boolean | Yes | Yes |
| **NOT **- Returns the negated value of parameter. Returns FALSE for TRUE value, and vice versa | Boolean | NOT(TRUE) = FALSE  
NOT(FALSE) = TRUE  
NOT(NULL) = NULL | value:  Initial value | Boolean | Yes | No |
| **TRUE** - Returns TRUE value | Boolean | TRUE() = TRUE | None | N/A | N/A | N/A |
| **FALSE** - Returns FALSE value | Boolean | FALSE() = FALSE | None | N/A | N/A | N/A |
| **IF ELSE** - Returns the value1 if condition is TRUE, otherwise returns value2 | ANY | IF ELSE(CONTAINS('The fox jumped over the fence', 'over'), 'Present', 'Absent') = 'Present'  
IF ELSE(CONTAINS('The fox jumped over the fence', 'wire'), 'Present', 'Absent') = 'Absent'  
IF ELSE(IS AFTER(NOW(), '1999-12-31T23:59:59.00+0530'), 'POST Y2K', 'PRE Y2K') = 'POST Y2K'  
IF ELSE(NULL, 10, 20) = NULL  
IF ELSE(TRUE, NULL, 20) = NULL  
IF ELSE(FALSE, NULL, 20) = 20 | condition:  Condition to determine which value to return | Boolean | Yes | No |
| value1:  Value to be returned if condition evaluates to TRUE | ANY | Yes | No |
| value2:  Value to be returned if condition evaluates to FALSE | ANY | Yes | No |
| **IF NULL** - Returns value1 if it is not null, otherwise returns value2.  
Function is useful to provide a default value, if the value of a field is not available (NULL) | ANY | IF NULL(100, 200) = 100  
IF NULL(100, NULL) = 100  
IF NULL(NULL, 100) = 100  
IF NULL(NULL, 'Hello') = 'Hello'  
IF NULL(NULL, NOW()) = '2019-04-11 15:27:45.631'  
IF NULL(NULL, TRUE) = TRUE  
IF NULL(NULL, NULL) = NULL | value1:  Initial value | ANY | Yes | No |
| value2:  Value to be returned if initial value is NULL | ANY | Yes | No |
| **COALESCE** - Returns the first value which is not null.  
Function is useful to pick the first field which has a value | ANY | COALESCE(100, 200, NULL, 300, NULL, 500) = 100  
COALESCE(NULL, NULL, 100, 200) = 100  
COALESCE(NULL, NULL, NULL, 'Hello') = 'Hello'  
COALESCE(NULL, NULL, NOW()) = '2019-04-11 15:48:20.548'  
COALESCE(NULL, TRUE) = TRUE  
COALESCE(NULL, NULL, NULL) = NULL | value1:  Initial value | ANY | Yes | No |
| value2:  Value to be returned if previous parameter values are NULL | ANY | Yes | Yes |