<< Click to Display Table of Contents >> Navigation:  Collect > Collect Enterprise > Template Designer Page > Formula Builder > Functions > Math Functions

The Math functions are able to compute integer and decimal data and will implicitly convert values to these types when used. Within EQuIS Collect, the functions perform mathematical calculations as data are entered in forms on the Mobile app. Within EQuIS Link, the functions provide a means to transform data when loading to a selected format.  

 

Below is a descriptive list of each math function and its operations. The examples are applicable to either fields in a Collect form or an EQuIS Link data source, unless otherwise noted.

ABS

The ABS function calculates the absolute value of a specific parameter. If the parameter cannot be converted to a numeric value or if it is NULL, then the function returns NULL.

ABS(<NumericParameter_1>)

Aggregate = No

 

Example 1

 

ABS([FieldA]) = 4

This formula is calculating the absolute value of FieldA.

 

Example 2

 

ABS([FieldA]) = NULL

Since FieldA is not numeric, this formula returns NULL.

ACOS

The ACOS function calculates the arc-cosine of the specified value (only a decimal value between -1 to 1). This function returns a degree. If the parameter is NULL, then the function returns NULL.

ACOS(<NumericParameter1>)

Aggregate = No

 

Example 1

 

ACOS(0.5) = 60

This formula is calculating the arc-cosine value of FieldA.

ADD

The ADD function adds the list of specified parameters together. If any of the parameters cannot be converted to a numeric value, then the function returns NULL. NULL parameter values are ignored and do not affect the result value.

ADD(<NumericParameter_1>,<NumericParameter_2>…<NumericParameter_N>)

Aggregate = No

 

Example

 

ADD([FieldA],[FieldB],[FieldC]) = 12

This formula is adding FieldA, FieldB, and FieldC together.

ASIN

The ASIN function calculates the arc-sine of the specified value (only a decimal value between -1 to 1). This function returns a degree. If the parameter is NULL, then the function returns NULL.

ASIN(<NumericParameter1>)

Aggregate = No

 

Example 1

 

Example: ASIN(0.5) = 30

This formula is calculating the arc-sine value of FieldA.

ATAN

The ATAN function calculates the arc-tan of the specified value (only a decimal value between -1 to 1). This function returns a degree. If the parameter is NULL, then the function returns NULL.

ATAN(<NumericParameter1>)

Aggregate = No

 

Example 1

 

Example: ATAN(0.5) = 0.577350269

This formula is calculating the arc-tan value of FieldA.

AVG

The AVG function returns the average of all specified parameters in all rows. Null parameter values are ignored and do not affect the result. If all values are NULL or there are no records, the function returns 0. If any parameter value cannot be converted to a numeric value, the function returns NULL.

AVG(<NumericParameter_1>,<NumericParameter_2>…<NumericParameter_N>)

Aggregate = Yes

 

Example 1 – Collect Form1

 

AVG([form1.FieldA]) = 4

This formula is calculating the average value of all rows from FieldA on Form1.

 

Example 2 – Collect Form1

 

AVG([form1.FieldA],[form1.FieldB]) = 5

This formula is calculating the average value of all rows from FieldA and FieldB on Form1. Note that the string values of FieldB are converted to their numeric equivalent.

 

Example 3 – Collect Form1

 

AVG([form1.FieldA],[form1.FieldB]) = NULL

This formula is trying to calculate the average value of all rows from FieldA and FieldB on Form1, but encountered a non-numeric value (‘X’) and therefore returns NULL.

CEILING

The CEILING function returns the smallest integer value that is greater than or equal to the specified decimal number. If the parameter is not numeric (cannot be converted to a number) or NULL, then the function returns NULL.

CEILING(<NumericParameter_1>)

Aggregate = No

 

Example 1

 

CEILING([FieldA]) = 16

This formula is returning closest integer value greater than the value in FieldA.

 

Example 2

 

CEILING([FieldA]) = 9

This formula is returning closest integer value greater than the value in FieldA.

 

Example 3

 

CEILING([FieldA]) = 8

This formula is returning the integer value of FieldA.

COS

The COS function calculates the cosine of the specified angle in degrees. If the parameter is NULL or cannot be converted to a numeric value, then the function returns NULL.

COS(<NumericParameter_1>)

Aggregate = No

 

Example

 

COS([FieldA]) = 0.965925826

This formula is calculating the cosine of FieldA.

COSH

The COSH function calculates the hyperbolic cosine of the specified angle. The angle must be in radians. If the parameter cannot be converted to a decimal or is NULL, then the function returns NULL.

COSH(<NumericParameter_1>)

Aggregate = No

 

Example

 

COSH([FieldA]) = 1.00500416805580

This formula is calculating the hyperbolic cosine of FieldA.

CUBICSPLINEMAXX

The CUBICSPLINEMAXX function generates a cubic spline interpolation using the non-null X and Y value pairs and returns the maximum X value within its domain. The first two numeric parameters define the X and Y value pair. An optional third parameter can be applied to define the string border condition as either "Natural" or "Quadratic". If the optional third parameter is not defined, it will default to apply a "Natural" string border condition. This function requires at least three data records to begin interpolation.

CUBICSPLINEMAXX(<NumericParameter_1>,<NumericParameter_2>,{<OptionalParamter_1>})

Aggregate = Yes

 

Example 1 - Form 1

 

CUBICSPLINEMAXX([Form 1.FieldA],[Form 1.FieldB]) = 9.43195800920748
This formula is interpolating the maximum X value from FieldA and FieldB values in Form1 and is using the “Natural” border string condition.

 

Example 2 - Form 1

 

CUBICSPLINEMAXX([Form 1.FieldA],[Form 1.FieldB],’Quadratic’) = 9.14453439710244
This formula is interpolating the maximum X value from FieldA and FieldB value in Form1 and is using the “Quadratic” string border condition.

CUBICSPLINEMAXY

The CUBICSPLINEMAXY function generates a cubic spline interpolation using the non-null X and Y value pairs and returns the maximum Y value within its domain. The first two numeric parameters define the X and Y value pair. An optional third parameter can be applied to define the string border condition as either "Natural" or "Quadratic". If the optional third parameter is not defined, it will default to apply a "Natural" string border condition. This function requires at least three data records to begin interpolation.

CUBICSPLINEMAXY(<NumericParameter_1>,<NumericParameter_2>,{<OptionalParamter_1>})

Aggregate = Yes

 

Example 1 - Form 1

 

CUBICSPLINEMAXY([Form 1.FieldA],[Form 1.FieldB]) = 128.778045294793
This formula is interpolating the maximum Y value from FieldA and FieldB values in Form1 and is using the “Natural” border string condition.

 

Example 2 - Form 1

 

CUBICSPLINEMAXY([Form 1.FieldA],[Form 1.FieldB],’Quadratic’) = 128.980620733472
This formula is interpolating the maximum Y value from FieldA and FieldB value in Form1 and is using the “Quadratic” string border condition.

CUBICSPLINEVALUE

The CUBICSPLINEVALUE function generates a cubic spline interpolation using the non-null X and Y value pairs as the first two numeric parameters and returns the function’s Y value when evaluated on a given X value as defined by the third numeric parameter. An optional fourth parameter can be applied to define the string border condition as either "Natural" or "Quadratic". If the optional fourth parameter is not defined, it will default to apply a "Natural" string border condition. This function requires at least three data records to begin interpolation.

CUBICSPLINEVALUE(<NumericParameter_1>,<NumericParameter_2>,<NumericParameter_3>,{<OptionalParamter_1>})

Aggregate = Yes

 

Example 1 - Form 1

 

CUBICSPLINEVALUE([Form 1.FieldA],[Form 1.FieldB],17.6381) = 109.585498690304
This formula is interpolating the Y value from FieldA and FieldB values in Form1 based on the defined X value of 17.6381, and is using the “Natural” border string condition.

 

Example 2 - Form 1

 

CUBICSPLINEVALUE([Form 1.FieldA],[Form 1.FieldB],17.6381) = 96.2157801462947
This formula is interpolating the Y value from FieldA and FieldB values in Form1 based on the defined X value of 17.6381, and is using the “Quadratic” border string condition.

DIVIDE

The DIVIDE function returns the division of two specified parameters (x/y). If the parameters cannot be converted to a decimal, are NULL or if the second parameter is 0, then the function returns NULL.

DIVIDE(<NumericParameter_1>,<NumericParameter_2>)

Aggregate = No

 

Example 1

 

DIVIDE([FieldA],[FieldB]) = 2

This formula is dividing FieldA by FieldB.

 

Example 2

 

DIVIDE([FieldA],[FieldB]) = NULL

This formula is trying to divide FieldA by FieldB but since FieldB is 0, it returns NULL.

EXP

The EXP function calculates the value of e raised to a specified power (ex). If the parameter is NULL or cannot be converted to a decimal, then the function returns NULL.

EXP(<NumericParameter_1>)

Aggregate = No

 

Example

 

EXP([FieldA]) = 54.5981500331

This formula is calculating the mathematical constant, e, raised to the power of FieldA.

FLOOR

The FLOOR function returns the largest integer less than or equal to the specified decimal number. If the parameter cannot be converted to a numeric value or is NULL, then the function returns NULL.

FLOOR(<NumericParameter_1>)

Aggregate = No

 

Example 1

 

FLOOR([FieldA]) = 15

This formula is returning closest integer value less than the value in FieldA.

 

Example 2

 

FLOOR([FieldA]) = 8

This formula is returning closest integer value less than the value in FieldA.

LOG

The LOG function calculates the logarithm of a specified parameter in a specified base (i.e., NumericParameter_2). If either of the two parameters are NULL or cannot be converted to a numeric value, then the function returns NULL.

LOG(<NumericParameter_1>,<NumericParameter_2>)

Aggregate = No

 

Example

 

LOG([FieldA],10) = 2

This formula is calculating the logarithm in base 10 of FieldA.

MAX

The MAX function returns the maximum value found for all specified parameters in all rows. Null parameter values are ignored and do not affect the result. If all values are NULL or there are no records, the function returns NULL. Values are converted to a common type before comparison. For example, if after processing a few rows the maximum value is 10 and then a string value is encountered, the string representation of ‘10’ will be compared against that string value.

MAX(<NumericParameter_1>,<NumericParameter_2>…<NumericParameter_N>)

Aggregate = Yes

 

Example 1 – Collect Form1

 

MAX([form1.FieldA],[form1.FieldB],[form1.FieldC]) = 131

This formula is calculating the maximum value of all rows from FieldA, FieldB, and FieldC on Form1.

 

Example 2 – Collect Form1

 

 

MAX([form1.FieldA],[form1.FieldB]) = 4

This formula is calculating the maximum value of all rows from FieldA and FieldB on Form1. Each comparison is valid as a numeric comparison. So the function is comparing values as numbers.

 

Example 3 – Collect Form1

 

 

MAX([form1.FieldA],[form1.FieldB]) = ‘A4’

This formula is calculating the maximum value of all rows from FieldA and FieldB on Form1. Each comparison is valid as a numeric comparison except for the last one, which compares 4 (the largest value at that point) against ‘A4’, which is string. So the final comparison takes the largest value between ‘4’ and ‘A4’.

MAXVAL

The MAXVAL function gets the largest parameter out of a list of parameters. This function can be used with any parameter type and ignores null values. If all values are NULL, then it returns NULL. If strings are used, then the comparison for that value is a string comparison.

MAXVAL(<NumericParameter_1>,<NumericParameter_2>…<NumericParameter_N>)

Aggregate = No

 

Example 1

 

MAXVAL([FieldA],[FieldB],[FieldC],[FieldD],[FieldE]) = 60

This formula is calculating the maximum value from FieldA, FieldB, FieldC, FieldD, and FieldE.

 

Example 2

 

MAXVAL([FieldA],[FieldB],[FieldC],[FieldD],[FieldE]) = ‘A8’

This formula is calculating the maximum value from FieldA, FieldB, FieldC, FieldD, and FieldE using string comparison since FIeldA is a string value.

MIN

The MIN function returns the minimum value found for all specified parameters in all rows. Null parameter values are ignored and do not affect the result. If all values are NULL or there are no records, the function returns NULL. Values are converted to a common type before comparison. For example, if after processing a few rows the minimum value is 10 and then a string value is encountered, the string representation of ‘10’ will be compared against that string value.

MIN(<NumericParameter_1>,<NumericParameter_2>…<NumericParameter_N>)

Aggregate = Yes

 

Example – Collect Form1

 

MIN([form1.FieldA],[form1.FieldB],[form1.FieldC]) = 0.7

This formula is calculating the minimum value of all rows from FieldA, FieldB, and FieldC.

MINVAL

The MINVAL function gets the smallest parameter out of a list of parameters. This function can be used with any parameter type and ignores null values. If all values are NULL, then it returns NULL. If strings are used, then the comparison for that value is a string comparison.

MINVAL(<NumericParameter_1>,<NumericParameter_2>…<NumericParameter_N>)

Aggregate = No

 

Example

 

MINVAL([FieldA],[FieldB],[FieldC],[FieldD],[FieldE]) = 8

This formula is calculating the minimum value from FieldA, FieldB, FieldC, FieldD, and FieldE.

MULTIPLY

The MULTIPLY function calculates the product of the specified parameters. If any of the parameters are NULL or cannot be converted to a number, then the function returns NULL.

MULTIPLY(<NumericParameter_1>,<NumericParameter_2>…<NumericParameter_N>)

Aggregate = No

 

Example

 

MULTIPLY([FieldA],[FieldB],[FieldC]) = 30

This formula is calculating the product of FieldA, FieldB, and FieldC.

POWER

The POWER function calculates the value of a specified parameter raised to a specified power (i.e., NumericParameter_2). If any of the parameters are NULL or cannot be converted to a numeric value, then the function returns NULL.

POWER(<NumericParameter_1>,<NumericParameter_2>)

Aggregate = No

 

Example

 

POWER([FieldA],2) = 16

This formula is calculating FieldA raised to the power 2, as specified by the second parameter.

 

PRODUCT

The PRODUCT function calculates the product of all the specified parameters in all rows. NULL values are ignored but if any of the values cannot be converted to a numeric value or if all values are NULL or if there are no rows, then the function returns NULL.

PRODUCT(<NumericParameter_1>,<NumericParameter_2>…<NumericParameter_N>)

Aggregate = Yes

 

Example – Collect Form1

 

PRODUCT([form1.FieldA],[form1.FieldB]) = 288

This formula is calculating the product of all rows from FieldA and FieldB on Form1.

RAND

The RAND function generates a random number. This function can take no parameters or 1 parameter. With no parameters or a null or empty parameter, the function returns a decimal number between 0 and 1. When a parameter is applied that can be converted to an integer value (decimal values are rounded), the function returns a random integer between 0 (inclusive) and N (non-inclusive), where N is equal to the parameters assigned integer value.

RAND({<OptionalNumericParameter_1>})

Aggregate = No

 

Example 1

 

RAND() = 0.88183345326598

This formula is returning a random decimal, 0.88183345326598, greater than or equal to 0 and less than 1.

 

Example 2

 

RAND([FieldA]) = 9

This formula is returning a random integer, 9 in this case, between 0 (inclusive) and 13 (non-inclusive).

ROUND

The ROUND function rounds a specified parameter to a specified number of fractional digits (i.e., NumericParameter_2). If either of the two parameters are NULL or cannot be converted to numeric values, then the function returns NULL. The second parameter will be converted to an integer value.

ROUND(<NumericParameter_1>,<NumericParameter_2>)

Aggregate = No

 

Example 1

 

ROUND([FieldA],1) = 10.4

This formula is rounding the FieldA value to 1 fractional digit, as specified by the second parameter.

 

Example 2

 

ROUND([FieldA],3) = 10.351

This formula is rounding the FieldA value to 3 fractional digits, as specified by the second parameter.

SIN

The SIN function calculates the sine of the specified angle in degrees. If the parameter cannot be converted to a numeric value or is NULL, then the function returns NULL.

SIN(<NumericParameter_1>)

Aggregate = No

 

Example

 

SIN([FieldA]) = 0.25881904510252

This formula is calculating the sine of FieldA.

SINH

The SINH function calculates the hyperbolic sine of the specified angle. The angle must be in radians. If the parameter is NULL or cannot be converted to a numeric value, then the function returns NULL.

SINH(<NumericParameter_1>)

Aggregate = No

 

Example

 

SINH([FieldA]) = 0.10016675001984403

This formula is calculating the hyperbolic sine of FieldA.

SQRT

The SQRT function calculates the square root of a specified parameter.

SQRT(<NumericParameter_1>)

Aggregate = No

 

Example

 

SQRT([FieldA]) = 2

This formula is calculating the square root of FieldA.

SUBTRACT

The SUBTRACT function returns the value of the first parameter minus the total value of the remaining parameters. If any of the parameters cannot be converted to a numeric value or are NULL, then the function returns NULL.

SUBTRACT(<NumericParameter_1>,<NumericParameter_2>…<NumericParameter_N>)

Aggregate = No

 

Example

 

SUBTRACT([FieldA],[FieldB],[FieldC]) = 2

This formula is subtracting FieldB and FieldC from FieldA.

SUM

The SUM function calculates the sum of all the specified parameters in all rows. NULL values are ignored but if there are no rows or values cannot be converted to numeric values, then the function returns NULL.

SUM(<NumericParameter_1>,<NumericParameter_2>…<NumericParameter_N>)

Aggregate = Yes

 

Example – Collect Form1

 

SUM([form1.FieldA],[form1.FieldB]) = 18

This formula is adding all rows of FieldA and FieldB on Form1 together.

TAN

The TAN function calculates the tangent of the specified angle in degrees. If the parameter cannot be converted to a numeric value or is NULL, then the function returns NULL.  

TAN(<NumericParameter_1>)

Aggregate = No

 

Example

 

TAN([FieldA]) = 0.2679491924311227

This formula is calculating the tangent of FieldA.

TANH

The TANH function calculates the hyperbolic tangent of the specified angle. The angle must be in radians. If the parameter cannot be converted to a numeric value or is NULL, then the function returns NULL.

TANH(<NumericParameter_1>)

Aggregate = No

 

Example

 

TANH([FieldA]) = 0.09966799462495582

This formula is calculating the hyperbolic tangent of FieldA.