Calculation Elements

What are Calculation Elements?

Calculation elements allow users to manipulate template elements in various configured equations to return calculations, return information, manipulate the contents of other cells, test conditions, and more.
This information is presented in real-time whilst completing a Template Record and can be used in Reporting.

Strings should use “double quotes”.

Function Help

Mathematical Calculations

Common Math Operators
Common Boolean Operators
SUM
PRODUCT
POWER
QUOTIENT
MOD
AVERAGE
MIN
MAX
EXP
SQRT

IF Statements and Logical Calculations

String Methods

&
EXACT
CONCAT
REPLACE
SUBSTITUTE

Date/Time Methods

TODAY
NOW
DATE
TIME
YEAR
MONTH
DAY
MINUTE
HOUR
SECOND
DAYS
EDATE
DATEDIF

**Common Math Operators (+,-,/,*, ^)**

Syntax - element/input (operator) element/input

Example:

(:calculation-1:*:calcualtion-1) + :calculation-1:/15 + :number-calc2:

Explanation:

This will return the result of the equation. BEDMAS will be used to evaluation longer equations.

Notes:
A string can be used with a basic math operator provided the number is at the beginning of the string. 1 + 2 + ‘3 - three' will = 6, however 1 + 2 + 'Three - 3’ will equal NaN and return an empty string.

Common Boolean Operators (<, >, =, <>, <=, >=)

Syntax - element/input (symbol) element/input

Example:

:element1: <= :element2 <=500

This will return TRUE or FALSE based on the logical expression.

Notes:

When comparing a string with a number, Catapult AMS will access them both alphabetically
An empty string will give NaN and yield an empty string.
A numerical value will always be less then a string value, except if the string starts with a digit which is lower than the numerical digit.

SUM

Syntax - SUM(element/input, element/input)

Example:

SUM(:num_element1:,:num_element2:, 50, 210)

This will return the sum of a series of numerical elements and/or inputted values.

Notes:

String: This will only give a result if the string starts with digits (for example '45 seconds').
Boolean: Will not work
Date: This will take the year
(for example 2020) and add that to the number

PRODUCT

Syntax - PRODUCT(element/input, element/input)

Example:

PRODUCT(:num_element1:,:num_element2:, 50, 210)

This will return the product of the numerical element and/or inputted values within the parentheses.

Notes:

String: This will only give a result if the string starts with digits (for example '45 seconds').
Boolean: Will not work
Date: This will take the year (for example 2020) and add that to the number

POWER

Syntax - POWER(element/input, exponent{element/input})

Example:

POWER(:num_element1:,3)

This will return a result of an element/input to the power of an element/input.

Note:

This will only work with numerical elements and strings elements that contain numbers only.

QUOTIENT

Syntax - QUOTIENT(starting element/input, element/input to divide by)

Example:

QUOTIENT(:num_element:,2)

This will return one element/input divided by another element/input. The result will be truncated (QUOTIENT(10,3) will return 3 not 3.33333)

Notes:

String: This will only give a result if the string contains digits only (for example '45').
Boolean: This will work with a 'true' value which will equal 1
Date: Will not work

MOD

Syntax - MOD(starting element/number, element/number to divide by)

Example:

MOD(:num_element:,3)

This will return the remainder of a quotient calculation. For example, MOD(10,3) will return 1 as 3 goes into 10 three times and there is 1 remainder.

Notes:

String: This will only give a result if the string contains digits only (for example '45').
Boolean: This will work with a 'true' value which will equal 1 so MOD whole numbers will always equal 0
Date: Will not work

AVERAGE

Syntax - AVERAGE(element/input, element/input)

Example:

AVERAGE(:num_element1:,3,:num_element2:)

This will return the average of the inputted elements/values between the parentheses.

Notes:

String: This will only give a result if the string starts with digits (for example '45 seconds').
Boolean: Will not work
Date: This will take the year
(for example 2020) and use that in the average

MIN

Syntax - MIN(element/input, element/input)

Example:

MIN(:num_element1:,3,:num_element2:)

This will return the minimum value of the inputted elements/values between the parentheses.

Notes:

String: This will only give a result if the string is only digits (for example '45').
Boolean: Will return 1 for true or 0 for false
Date: Will not work

MAX

Syntax - MIN(element/input, element/input)

Example:

MAX(:num_element1:,3,:num_element2:)

This will return the maximum value of the inputted elements/values between the parentheses.

Notes

String: This will only give a result if the string is only digits (for example '45').
Boolean: Will return 1 for true or 0 for false
Date: Will not work

EXP

Syntax - EXP(element/input)

Example:

EXP(:num_element1:)

This will return Euler's number (~2.718) to the power of the inputted element/value.

Notes

String: This will only give a result if the string is only digits (for example '45').
Boolean: Will return 1 for true or 0 for false
Date: Will not work

SQRT

Syntax - SQRT(element/input)

Example:

 SQRT(64)

This will return the positive square root of the inputted element/value.

Notes

String: This will only give a result if the string is only digits (for example '45').
Boolean: Will return 1 for true or 0 for false
Date: Will not work

IF

Syntax -

IF(
logical_expression,
value if true,
value if false
)

Example:

IF(
  :num_element1:<800
  "TRUE",
  "FALSE"
  )

This returns the value if TRUE OR value if FALSE condition inputted in the equation.

Notes

Double quotes must be used for string values and dates.
Dates will take the format “YYYY-MM-DD“.
A boolean must be TRUE or FALSE.

IFS

Syntax -

IFS(
logical_expression, value if true,
logical_expression2, value if true,
etc
)

Example:

IFS(
  :num_element1: > 5, "First True"
  :num_element2: < 10, "Second True"
  )

Evaluates multiple conditions and returns a value that corresponds to the first TRUE condition.

Notes

Double quotes must be used for string values and dates.
Dates will take the format “YYYY-MM-DD“.
A boolean must be TRUE or FALSE.

AND

Syntax -

AND(
logical_expression 1,
logical_expression 2,
logical_expression 3
)

Example:

AND(:num_element1: > 5,:num_element2: < 10)

This will evaluate the logical expression(s) and return TRUE if ALL are true, otherwise it will return false.

This is useful inside an IF or IFS statement when multiple conditions need to be met and custom text displayed.

Notes

Double quotes must be used for string values and dates.
Dates will take the format “YYYY-MM-DD“.
A boolean must be TRUE or FALSE.

OR

Syntax -

OR(
logical_expression 1,
logical_expression 2,
logical_expression 3
)

Example:

OR(:num_element1: > 5,:num_element2: < 10)

This will evaluate the logical expression(s) and return TRUE if any are true, otherwise, it will return false.

This is useful inside an IF or IFS statement when multiple conditions can be met and custom text displayed.

Notes

Double quotes must be used for string values and dates.
Dates will take the format “YYYY-MM-DD“.
A boolean must be TRUE or FALSE.

NOT

Syntax -

NOT(
logical_expression
)

Example:

NOT(:num_element1: <15)

This will return the opposite of the logical_expression.

Notes

Double quotes must be used for string values and dates.
Dates will take the format “YYYY-MM-DD“.
A boolean must be TRUE or FALSE.

&

Syntax - element1 & element2

Example:

:num_element1:&:num_element2:

This will concatenate two elements. If used on numerical elements 15 & 2 will result in 152.

Notes

Strings should use “double quotes”.
Dates should also use "double-quotes". Inputting a date of 2020-10-8 will treat it as a math sum and return 2002 (2020 minus 10 minus 8).
Booleans can be concatenated by inputting TRUE or FALSE. This will concatenate true or false to the calculation.

EXACT

Syntax -

EXACT(
string1,
string2
)

Example:

 EXACT(:string_element1:,:string_element2:)

This will return TRUE if two string values are identical.

Notes

Two boolean elements can be compared.
A string and date can be compared.
A boolean and string or numbers does not work.
A number and string will work.

CONCAT

Syntax -

CONCAT(
value1,
value2
)

Example:

 CONCAT(:string_element1:,"and",:string_element2:)

This will return the concatenation of the elements/input within the parentheses ie. Con and Cat

Notes

All data types can be concatenated.

REPLACE

Syntax -

REPLACE(
text,
position,
length,
new_text
)

Example:

 REPLACE(:string_element1:,2,3,"ESTING"

This will replace part of a string with new configured text.

text - The text, a part of which will be replaced.

position - The position where the replacement will begin (starting from 1).

length - The number of characters in the text to be replaced.

new_text - The text which will be inserted into the original text.

Notes

Booleans will not work but all other data types are fine.

SUBSTITUTE

Syntax -

SUBSTITUTE(
element,
what needs to be substituted,
what will be replaced
)

Example:

 SUBSITUTE(string_element1:,"t", "T")

This will replace the text of an element of part of the text of an element with new text.

In this example, we simply replace a lower case t with an upper case T from the string element.

Notes

Booleans will not work but all other data types are fine.

TODAY

Syntax - Today()

Example:

TODAY()

This will return the current date.

Notes

Values inside the parentheses will not do anything. This function should just be called by the use of the ().
Using TODAY() in a Math equation will give the year value (for example 2020).

NOW

Syntax - NOW()

Example:

 NOW()

This will return the current day and time

Notes

Values inside the parentheses will not do anything. This function should just be called by the use of the ().
Using NOW() in a Math equation will give the year value (for example 2020).

DATE

Syntax - DATE(year, month, date)

Example:

 DATE(2020,12,25)

This will convert in the inputted year, month and date value into a calendar date.

Notes

Booleans will give a value of 1.
String values will return the current day if they have any non-digit value in it.
A string with non-digits configured as the month will return NaN and should not be used.
A string with non-digits configures as the year value will return the current year.
An empty string will behave the following ways:
- If it is configured as the DAY value it will have a value of -1 and subtract one day from the total. For example :string-1: is empty so DATE(2020,10,:string-1:) will return 2020-09-30.
- If it is configured as the MONTH it will take the current month and subtract that from the configured date. For example, a value of DATE(2020,””,01) will return 2019-12-01 if the current month is August (8th Month).
- If it is configured as the YEAR value if will return 0000.
An input of over 12 for months or Month day count will add the values to the date returned. For example DATE(2020,13,01) would return 2021-01-01 and DATE(2020,10,40) would return 2020-11-09.

TIME

Syntax - TIME(hour, minute, second)

Example:

 TIME(12,5,54)

This will convert in inputted hour, minute and seconds value into a time.

Notes

Booleans will give a value of 1.
An empty string will give a value of 0.
A string containing non-digits will return the current value (either HH, MM or SS depending where it is configured).
Dates will be ignored and just return the current value (either HH, MM or SS depending where it is configured).

YEAR

Syntax - YEAR(date or DateTime element)

Example:

 YEAR(:datetime_element:)

This will return the year value of the date or DateTime element.

Notes

This function can be called without any arguments. YEAR() will return the current year.
Any strings that are not dates or booleans will return “Invalid Date“.

MONTH

Syntax - MONTH(date or DateTime element)

Example:

MONTH(:datetime_element:)

This will return the month value (a number) of the date or DateTime element.

Notes

This function can be called without any arguments. MONTH() will return the current month.
Any strings that are not dates or booleans will return “Invalid Date“.

DAY

Syntax - DAY(date or DateTime element)

Example:

DAY(:datetime_element:)

This will return the day value of the date or DateTime element.

Notes

This function can be called without any arguments. DAY() will return the current day.
Any strings that are not dates or booleans will return “Invalid Date“.

MINUTE

Syntax - MINUTE(datetime element)

Example:

MINUTE(:datetime_element:)

This will return the minute value of the DateTime element.

Notes

This function can be called without any arguments. MINUTE() will return the current minute.
Any strings that are not dates or booleans will return “Invalid Date“.

HOUR

Syntax - HOUR(datetime element)

Example:

 HOUR(:datetime_element:)

This will return the hour value of the DateTime element.

Notes

This function can be called without any arguments. HOUR() will return the current hour.
Any strings that are not dates or booleans will return “Invalid Date“.

SECOND

Syntax - SECOND(datetime element)

Example:

 SECOND(:datetime_element:)

This will return the hour value of the DateTime element.

Notes

This function can be called without any arguments. SECOND() will return the current hour.
Any strings that are not dates or booleans will return “Invalid Date“.

Notes

DAYS

Syntax - DAYS(start date/datetime element, end date/datetime element)

Example:

 DAYS(:required_date_element:,:datetime_element:)

This will return the number of days between the two configured dates.

Notes

Strings can boolean cannot be used.
Number elements can be used but should only be used if they represent a year. For example DAYS(:number1:,:current-date:) where the current date is 2020-08-14 and :number1: equals 2020 will return 225 as it is the 225th day of the year.
Any numbers inputted as arguments into the calculation will be treated as the beginning of UNIX (1970).

EDATE

Syntax - EDATE(start date/datetime element, number of months +- start date)

Example:

 EDATE(:required_date_element:,14)

This will return a date value based on the number of months before or after the start date value.

Notes

Strings can boolean cannot be used.
Number elements can be used but should only be used if they represent a year.
Any numbers inputted as arguments into the calculation will be treated as the beginning of UNIX (1970).

DATEDIF

Syntax - DATEDIF(start date/datetime element, end date/datetime element, unit)

Example:

 DATEIF(:datetime_element1:,:date_time_element2:,"D")

This will return the number of days, months or years, hours. minutes or seconds (whichever is configured as the unit) between the start and end DateTime.

Notes

Unit will equal ‘Y', ‘M’ or ‘D’, ‘hh’, ‘mm’ or 'ss’.

What are Calculation Elements?

Common Math Operators (+,-,/,*, ^)

Common Boolean Operators (<, >, =, <>, <=, >=)

SUM

PRODUCT

POWER

QUOTIENT

MOD

AVERAGE

MIN

MAX

EXP

SQRT

IF

IFS

AND

OR

NOT

&

EXACT

CONCAT

REPLACE

SUBSTITUTE

TODAY

NOW

DATE

TIME

YEAR

MONTH

DAY

MINUTE

HOUR

SECOND

DAYS

EDATE

DATEDIF

Related articles

**Common Math Operators (+,-,/,*, ^)**