Page History
Learn more about the math functions available in Blueriq expressions, such as:
- Basic math functions ADD, SUBTRACT, MULTIPLY, and DIVIDE;
- More advanced math functions like SUM, PRODUCT, DIV, MOD, POWER, and LOG;
- Rounding functions ROUND, ROUNDDOWN, ROUNDUP, ROUND TO MULTIPLE, ROUNDSIG, ROUNDSIGUP, and ROUNDSIGDOWN;
- Comparison functions EQUAL, NOT EQUAL, LESS, LESS OR EQUAL, GREATER, and GREATER OR EQUAL;
- And the special function ANNUITY.
Overview
Function | Description |
---|---|
ADD (+) |
Table of Contents | ||||
---|---|---|---|---|
|
...
SUM
This function adds the values of a collection of numbers.
Syntax
Code Block |
---|
SUM(collection) |
- collection - A collection of number values.
Return types
- number
- integer
- currency
- percentage
Examples
Suppose the following data model.
...
- SUM(COLLECT Person.age FROM ALL Person) results in 152
- SUM(COLLECT Person.age FROM ALL Person WHERE (Person.gender = “m”)) results in 96
- SUM([1,2,3,4,5,6,7,8,9,10]) results in 55
SUBTRACT
Use the '+' (plus) sign to add values of some type of number. |
Syntax
Code Block |
---|
number1 + number2 + ... |
- number1 - First number or attribute of some type of number to add.
- number2 - Second number or attribute of some type of number to add.
Return types
- number + number = number
- integer + integer = integer
- currency + currency = currency
- percentage + percentage = percentage
- number + integer = number
- number + currency = currency
- number + percentage = percentage
- integer + currency = currency
- integer + percentage = percentage
Examples
Suppose the following data model. Attribute Child.pocketMoney is of type currency and has a value of 2.50. Attribute Bonus.standard is of type percentage and has a value of 5.00.
- Child.pocketMoney + 3.00 = 5.50 of type currency
- Child.pocketMoney + 3 = 5.50 of type currency
- 2.50 + 3 = 5.5000 of type number
- 100 + Bonus.standard = 105.00 of type percentage
- Child.pocketMoney + Bonus.standard results in an error
Use the '-' (minus) sign to subtract values of some type of number. |
Syntax
Code Block |
---|
number1 - number2 - ... |
...
MULTIPLY (*) | Use the '*' (asterisk) sign to multiply values |
...
of some type of number |
...
. |
Return types
- number - number = number
- integer - integer = integer
- currency - currency = currency
- percentage - percentage = percentage
- number - integer = number
- number - currency = currency
- number - percentage = percentage
- integer - number = number
- integer - currency = currency
- integer - percentage = percentage
- currency - number = currency
- currency - integer = currency
- percentage - number = percentage
- percentage - integer = percentage
Examples
Suppose the following data model. Attribute Child.pocketMoney is of type currency and has a value of 2.50. Attribute Bonus.standard is of type percentage and has a value of 5.00.
- Child.pocketMoney - 1.00 = 1.50 of type currency
- Child.pocketMoney - 3 = -0.50 of type currency
- 5.00 - 3 = 2.0000 of type number
- 100 - Bonus.standard = 95.00 of type percentage
- Child.pocketMoney - Bonus.standard results in an error
DIVIDE (/) | Use the '/' (forward slash) sign to divide values of some type of number. |
Syntax
Code Block |
---|
number1 / number2 / ... |
- number1 - Dividend; number or attribute of some type of number to be divided by other number values.
- number2 - Divisor; number or attribute of some type of number to divide by.
Return types
- number / number = number
- integer / integer = number
- currency / currency = number
- percentage / percentage = number
- number / integer = number
- integer / number = number
- currency / number = currency
- currency / integer = currency
- percentage / number = percentage
- percentage / integer = percentage
Examples
Suppose the following data model. Attribute Child.pocketMoney is of type currency and has a value of 2.50. Attribute Bonus.standard is of type percentage and has a value of 5.00.
- Child.pocketMoney / 1.00 = 2.50 of type currency
- Child.pocketMoney / 2 = 1.25 of type currency
- 5.00 / 3 = 1.6667 of type number
- Bonus.standard / 5 = 1.00 of type percentage
SUM | Adds the values of a collection of numbers. |
PRODUCT | Returns the product of the values of a collection of numbers. |
DIV | Returns |
DIV
...
the integer portion of a division. |
...
Syntax
...
...
number1 DIV number2
- number1 - Dividend; number or attribute of some type of number to be divided by another number value.
- number2 - Divisor; number or attribute of some type of number to divide by.
Return type
- integer
Examples
Suppose the following data model. Attribute Child.pocketMoney is of type currency and has a value of 2.50. Attribute Bonus.standard is of type percentage and has a value of 5.00.
- Child.pocketMoney DIV 1.00 = 2 of type integer
- Bonus.standard DIV 5 = 1 of type integer
- 6 DIV 4.50 = 1 of type integer
MOD | Returns |
MOD
...
the remainder of a division. |
Syntax
Code Block |
---|
number1 % number2 |
- number1 - Dividend; number or attribute of some type of number to be divided by another number value.
- number2 - Divisor; number or attribute of some type of number to divide by.
Return types
- number % number = number
- integer % integer = integer
- currency % currency = currency
- percentage % percentage = percentage
- number % integer = number
- integer % number = number
- currency % number = currency
- currency % integer = currency
- percentage % number = percentage
- percentage % integer = percentage
Examples
Suppose the following data model. Attribute Child.pocketMoney is of type currency and has a value of 2.50. Attribute Bonus.standard is of type percentage and has a value of 5.00.
- Child.pocketMoney % 0.20 = 0.10 of type currency
- Child.pocketMoney % 1 = 0.50 of type currency
- Bonus.standard % 3 = 2.00 of type percentage
- 9 % 2.50 = 1.5000 of type number
POWER (**) | Use the '**' operator to raise a number to a power. |
LOG | Calculates the logarithm of a number to a base. |
ROUND | Rounds |
ROUND
...
a number value to a specified number of decimal places. |
Syntax
Code Block |
---|
ROUND number [UP | DOWN] TO POSITION position |
- number - Number or attribute of some type of number to be rounded.
- UP or DOWN - The direction of rounding. The direction DOWN points to zero for both positive and negative values, UP points away from zero. If no direction is specified, rounding will be done to the nearest integer (half-way values will be rounded up).
- position - The rounding position as integer. A positive integer represents the place value of the digit after the decimal point. Zero and negative integers represent the place value of the digit before the decimal point. Place values: [-n]..[-3][-2][-1][0].[1][2][3]..[n]
Return types
- number
- integer
- currency
- percentage
Examples
- ROUND 12345.23 TO POSITION -2 = 12300.0000
- ROUND 12345.23 TO POSITION 1 = 12345.2000
- ROUND 12345.23 UP TO POSITION -2 = 12400
- ROUND 12345.23 UP TO POSITION 1 = 12345.3000
- ROUND 12345.23 DOWN TO POSITION -2 = 12300.0000
- ROUND 12345.23 DOWN TO POSITION 1 = 12345.2000
- ROUND 12345.23 UP TO POSITION -5 = 100000.0000
- ROUND 12345.23 TO POSITION -5 = 0.0000
ROUND DOWN | Rounds a number value down to a specified number of decimal places. |
ROUND UP | Rounds a number value up to a specified number of decimal places. |
ROUND TO MULTIPLE | Rounds a number value to a specified multiple. |
ROUND UP TO MULTIPLE | Rounds a number value up to a specified multiple. |
ROUND DOWN TO MULTIPLE | Rounds a number value down to a specified multiple. |
ROUNDSIG | Alternative syntax for ROUND TO MULTIPLE. |
ROUNDSIGUP | Alternative syntax for ROUND UP TO MULTIPLE. |
ROUNDSIGDOWN | Alternative syntax for ROUND DOWN TO MULTIPLE. |
EQUAL (=) | The operator '=' compares two values and returns the boolean value TRUE if the left value is equal to the right value and FALSE otherwise. |
NOT EQUAL (!=) | The operator '!= |
ROUND TO MULTIPLE
The ROUND TO MULTIPLE function rounds a number value to a specified multiple.
Syntax
Code Block |
---|
ROUND number [UP | DOWN] TO MULTIPLE multiple |
- number - Number or attribute of some type of number to be rounded.
- UP or DOWN - The direction of rounding. The direction DOWN points to zero for both positive and negative values, UP points away from zero. If no direction is specified, rounding will be done to the nearest multiple (half-way values will be rounded up).
- multiple - The multiple to be rounded to. This can be both a positive and negative value of any number type.
Return types
- number
- integer
- currency
- percentage
Examples
- ROUND 12345.23 TO MULTIPLE 500 = 12500.0000 of type number
- ROUND 12345.23 TO MULTIPLE 5 = 12345.0000 of type number
- ROUND 12345.23 TO MULTIPLE 0.50 = 12345.0000 of type number
MULTIPLY
Use the '*' (asterisk) sign to multiply values of some type of number.
Syntax
Code Block |
---|
number1 * number2 * ... |
- number1 - First factor; number or attribute of some type of number.
- number2 - Second factor; number or attribute of some type of number.
Return types
- number * number = number
- integer * integer = number
- currency * currency = number
- percentage * percentage = number
- number * integer = number
- integer * number = number
- currency * number = currency
- currency * integer = currency
- percentage * number = percentage
- percentage * integer = percentage
Examples
Suppose the following data model. Attribute Child.pocketMoney is of type currency and has a value of 2.50. Attribute Bonus.standard is of type percentage and has a value of 5.00.
- Child.pocketMoney * 1.00 = 2.50 of type currency
- Child.pocketMoney * 2 = 5.00 of type currency
- 5.00 * 3 = 15.0000 of type number
- Bonus.standard * 5 = 25.00 of type percentage
PRODUCT
This function returns the product of the values of a collection of numbers.
Syntax
Code Block |
---|
PRODUCT(collection) |
- collection - A collection of number values.
Return types
- number
- integer
- currency
- percentage
Examples
Suppose the following data model.
...
- PRODUCT(COLLECT Person.score FROM ALL Person) results in 10327.5000
- PRODUCT(COLLECT Person.score FROM ALL Person WHERE (Person.score < 8)) results in 135.0000
- PRODUCT([3.5, 2.2]) results in 7.7000 of type number
POWER
Use the '**' operator to raise a number to a power.
Syntax
Code Block |
---|
base ** exponent |
- base - Base; number or attribute of some type of number.
- exponent - Exponent, power; number or attribute of some type of number.
Return types
- number ** number = number
- integer ** integer = number
- number ** integer = number
- integer ** number = number
- currency ** number = currency
- currency ** integer = currency
- percentage ** number = percentage
- percentage ** integer = percentage
Examples
Suppose the following data model. Attribute Child.pocketMoney is of type currency and has a value of 2.50. Attribute Bonus.standard is of type percentage and has a value of 5.00.
- Child.pocketMoney ** 1.00 = 2.50 of type currency
- Child.pocketMoney ** 2 = 6.25 of type currency
- 5.00 ** 3 = 125.0000 of type number
- Bonus.standard ** 5 = 3125.00 of type percentage
LESS
...
' compares two values and returns the boolean value TRUE if the left value is |
...
LESS OR EQUAL
not equal to the right value and FALSE otherwise. |
Syntax
Code Block |
---|
value1 < value2 |
- value1 - Number, date or attribute of some type of number or date.
- value2 - Number, date or attribute of some type of number or date.
Return type
- boolean
Examples
- 1 < 2 returns TRUE
- 1.0 < 1.0 returns FALSE
- DATE(2012,1,1) < TODAY returns TRUE
- 2000 < YEAR(TODAY) returns TRUE
The operator '< |
...
' compares two values and returns the boolean value TRUE if the left value is smaller than |
...
the right value and FALSE otherwise. |
Syntax
Code Block |
---|
value1 <= value2 |
- value1 - Number, date or attribute of some type of number or date.
- value2 - Number, date or attribute of some type of number or date.
Return type
- boolean
Examples
- 1 <= 2 returns TRUE
- 1.0 <= 1.0 returns TRUE
- DATE(2012,1,1) <= TODAY returns TRUE
- 4000 <= YEAR(TODAY) returns FALSE
LESS OR EQUAL (<=) | The operator ' |
...
<=' compares two values and returns the boolean value TRUE if the left value is |
...
smaller than or equal to the right value and FALSE otherwise. |
Syntax
Code Block |
---|
value1 > value2 |
- value1 - Number, date or attribute of some type of number or date.
- value2 - Number, date or attribute of some type of number or date.
Return type
- boolean
Examples
- 1 > 2 returns FALSE
- 1.0 > 1.0 returns FALSE
- DATE(2012,1,1) > TODAY returns FALSE
- 4000 > YEAR(TODAY) returns TRUE
GREATER (>) | The operator '> |
...
' compares two values and returns the boolean value TRUE if the left value is greater than |
...
the right value and FALSE otherwise. |
Syntax
Code Block |
---|
value1 >= value2 |
- value1 - Number, date or attribute of some type of number or date.
- value2 - Number, date or attribute of some type of number or date.
Return type
- boolean
Examples
- 1 >= 2 returns FALSE
- 1.0 >= 1.0 returns TRUE
- DATE(2012,1,1) >= TODAY returns FALSE
- 4000 >= YEAR(TODAY) returns TRUE
GREATER OR EQUAL (>=) | The operator '>=' compares two values and returns the boolean value TRUE if the left value is greater than or equal to the right value and FALSE otherwise. |
...
ANNUITY | Computes an annuity. An annuity is a terminating "stream" of fixed payments. |
Functions
Include Page | ||||
---|---|---|---|---|
|
Include Page | ||||
---|---|---|---|---|
|
Include Page | ||||
---|---|---|---|---|
|
Include Page | ||||
---|---|---|---|---|
|
Include Page | ||||
---|---|---|---|---|
|
Include Page | ||||
---|---|---|---|---|
|
Include Page | ||||
---|---|---|---|---|
|
Include Page | ||||
---|---|---|---|---|
|
Include Page | ||||
---|---|---|---|---|
|
Include Page | ||||
---|---|---|---|---|
|
Include Page | ||||
---|---|---|---|---|
|
Include Page | ||||
---|---|---|---|---|
|
Include Page | ||||
---|---|---|---|---|
|
Include Page | ||||
---|---|---|---|---|
|
Include Page | ||||
---|---|---|---|---|
|
Include Page | ||||
---|---|---|---|---|
|
Include Page | ||||
---|---|---|---|---|
|
Include Page | ||||
---|---|---|---|---|
|
Include Page | ||||
---|---|---|---|---|
|
Syntax
Code Block |
---|
value1 = value2 |
- value1 - Number, date or attribute of some type of number or date.
- value2 - Number, date or attribute of some type of number or date.
For value list, the '=' operator is implemented as SUBSET OF. To test if two value lists are equal you could use the expression:
Code Block |
---|
L1 SUBSET OF L2 AND L2 SUBSET OF L1 |
Return type
- boolean
Examples
- 1 = 2 returns FALSE
- 1.0 = 1.0 returns TRUE
- DATE(2012,1,1) = TODAY returns FALSE
- 5 + 5 = 10 returns TRUE
NOT EQUAL
The operator '!=' compares two values and returns the boolean value TRUE if the left value is not equal to the right value and FALSE otherwise.
Syntax
Code Block |
---|
value1 != value2 |
- value1 - Number, date or attribute of some type of number or date.
- value2 - Number, date or attribute of some type of number or date.
Return type
- boolean
Examples
...
...
...
...