You are viewing the documentation for Blueriq 17. Documentation for other versions is available in our documentation directory.
ADD
Use the '+' (plus) sign to add values of some type of number.
Syntax
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
- currency + percentage = error
Examples
Suppose the following data model:
Entitiy.attribute | Value | Type |
---|---|---|
Child.pocketMoney | 2.50 | Currency |
Bonus.standard | 5.00 | Percentage |
Expression | Result | Type |
---|---|---|
Child.pocketMoney + 3.00 | 5.50 | Currency |
Child.pocketMoney + 3 | 5.50 | Currency |
2.50 + 3 | 5.5000 | Number |
100 + Bonus.standard | 105.00 | Percentage |
Child.pocketMoney + Bonus.standard | Error |
SUM
This function adds the values of a collection of numbers.
Syntax
SUM ( collection )
- collection - A collection of number values.
Return types
- number
- integer
- currency
- percentage
Examples
Suppose the following data model.
Person.name | Person.gender | Person.age |
---|---|---|
“Kim” | “f” | 23 |
“Rick” | “m” | 35 |
“Bob” | “m” | 42 |
“John” | “m” | 19 |
“Mary” | “f” | 33 |
Expression | Result | Type |
---|---|---|
SUM ( COLLECT Person.age FROM ALL Person ) | 152 | Integer |
SUM ( COLLECT Person.age FROM ALL Person WHERE ( Person.gender = "m" ) ) | 96 | Integer |
SUM ( [ 1 , 2 , 3 , 4 , 5 , 6 , 7 , 8 , 9 , 10 ] ) | 55 | Integer |
SUBTRACT
Use the '-' (minus) sign to subtract values of some type of number.
Syntax
number1 - number2 - ...
- number1 - Number or attribute of some type of number to subtract other number values from.
- number2 - Number or attribute of some type of number to subtract.
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
- currency - percentage = error
- percentage - currency = error
Examples
Suppose the following data model.
Entitiy.attribute | Value | Type |
---|---|---|
Child.pocketMoney | 2.50 | Currency |
Bonus.standard | 5.00 | Percentage |
Expression | Result | Type |
---|---|---|
Child.pocketMoney - 1.00 | 1.50 | Currency |
Child.pocketMoney - 3 | -0.50 | Currency |
5.00 - 3 | 2.0000 | Number |
100 - Bonus.standard | 95.00 | Percentage |
Child.pocketMoney - Bonus.standard | Error |
MULTIPLY
Use the '*' (asterisk) sign to multiply values of some type of number.
Syntax
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
- currency * percentage = currency
Examples
Suppose the following data model.
Entitiy.attribute | Value | Type |
---|---|---|
Child.pocketMoney | 2.50 | Currency |
Bonus.standard | 5.00 | Percentage |
Expression | Value | Type |
---|---|---|
Child.pocketMoney * 1.00 | 2.50 | Currency |
Child.pocketMoney * 2 | 5.00 | Currency |
5.00 * 3 | 15.0000 | Number |
Bonus.standard * 5 | 25.00 | Percentage |
PRODUCT
This function returns the product of the values of a collection of numbers.
Syntax
PRODUCT ( collection )
- collection - A collection of number values.
Return types
- number
- integer
- currency
- percentage
Examples
Suppose the following data model.
Person.name | Person.score |
---|---|
“Kim” | 9 |
“Rick” | 8.5 |
“Bob” | 3 |
“John” | 7.5 |
“Mary” | 6 |
Expression | Value | Type |
---|---|---|
PRODUCT ( COLLECT Person.score FROM ALL Person ) | 10327.5000 | Number |
PRODUCT ( COLLECT Person.score FROM ALL Person WHERE ( Person.score < 8 ) ) | 135.0000 | Number |
PRODUCT ( [ 3.5 , 2.2 ] ) | 7.7000 | Number |
DIVIDE
Use the '/' (forward slash) sign to divide values of some type of number.
Syntax
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
- currency / percentage = currency
Examples
Suppose the following data model.
Entitiy.attribute | Value | Type |
---|---|---|
Child.pocketMoney | 2.50 | Currency |
Bonus.standard | 5.00 | Percentage |
Expression | Result | Type |
---|---|---|
Child.pocketMoney / 1.00 | 2.50 | Currency |
Child.pocketMoney / 2 | 1.25 | Currency |
5.00 / 3 | 1.6667 | Number |
Bonus.standard / 5 | 1.00 | Percentage |
DIV
This function returns 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.
Entitiy.attribute | Value | Type |
---|---|---|
Child.pocketMoney | 2.50 | Currency |
Bonus.standard | 5.00 | Percentage |
Expression | Result | Type |
---|---|---|
Child.pocketMoney DIV 1.00 | 2 | Integer |
Bonus.standard DIV 5 | 1 | Integer |
6 DIV 4.50 | 1 | Integer |
MOD
This function returns the remainder of a division.
Syntax
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.
Entitiy.attribute | Value | Type |
---|---|---|
Child.pocketMoney | 2.50 | Currency |
Bonus.standard | 5.00 | Percentage |
Expression | Result | Type |
---|---|---|
Child.pocketMoney % 0.20 | 0.10 | Currency |
Child.pocketMoney % 1 | 0.50 | Currency |
Bonus.standard % 3 | 2.00 | Percentage |
9 % 2.50 | 1.5000 | Number |
POWER
Use the '**' operator to raise a number to a power.
Syntax
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.
Entitiy.attribute | Value | Type |
---|---|---|
Child.pocketMoney | 2.50 | Currency |
Bonus.standard | 5.00 | Percentage |
Expression | Result | Type |
---|---|---|
Child.pocketMoney ** 1.00 | 2.50 | Currency |
Child.pocketMoney ** 2 | 6.25 | Currency |
5.00 ** 3 | 125.0000 | Number |
Bonus.standard ** 5 | 3125.00 | Percentage |
LOG
Since 12.8
Use the 'LOG' function to calculate the logarithm of a number to a base.
Syntax
LOG([number], [base])
number- Number; number or attribute of some type of number.
- base - Base; number or attribute of some type of number.
Return types
number
Examples
Expression | Result | Type |
---|---|---|
LOG(100, 10) | 2.0 | Number |
LOG(32, 2) | 5.0 | Number |
LOG(3, 9) | 0.5 | Number |
ROUND
ROUNDDOWN
ROUNDUP
The ROUND function rounds a number value to a specified number of decimal places.
Syntax
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
If you prefer a functional syntax, then you can use the ROUNDDOWN or ROUNDUP function. Its syntax is ROUNDDOWN/ROUNDUP ( number , digits ). Example: ROUNDDOWN( 1.23456 , 2 ) = 1.23
Examples
Expression | Alternative syntax | Result | Type |
---|---|---|---|
ROUND 12345.23 TO POSITION -2 | ROUNDSIG( 12345.23, 100 ) | 12300.0000 | Number |
ROUND 12345.23 TO POSITION 1 | ROUNDSIG( 12345.23 , 0.1 ) | 12345.2000 | Number |
ROUND 12345.23 UP TO POSITION -2 | ROUNDUP( 12345.23 , -2 ) | 12400 | Integer |
ROUND 12345.23 UP TO POSITION 1 | ROUNDUP( 12345.23 , 1 ) | 12345.3000 | Number |
ROUND 12345.23 DOWN TO POSITION -2 | ROUNDDOWN( 12345.23 , -2 ) | 12300.0000 | Number |
ROUND 12345.23 DOWN TO POSITION 1 | ROUNDDOWN( 12345.23 , 1 ) | 12345.2000 | Number |
ROUND 12345.23 UP TO POSITION -5 | ROUNDUP( 12345.23 , -5 ) | 100000.0000 | Number |
ROUND TO MULTIPLE
ROUNDSIG
ROUNDSIGUP
ROUNDSIGDOWN
The ROUND TO MULTIPLE function rounds a number value to a specified multiple.
Syntax
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
Expression | Alternative syntax | Result | Type |
---|---|---|---|
ROUND 12345.23 TO MULTIPLE 500 | ROUNDSIG( 12345.23, 500 ) | 12500.0000 | Number |
ROUND 12345.23 TO MULTIPLE 5 | ROUNDSIG( 12345.23 , 5 ) | 12345.0000 | Number |
ROUND 12345.23 TO MULTIPLE 0.50 | ROUNDSIG( 12345.23 , 0.50 ) | 12345.0000 | Number |
ROUND 12345.27 TO MULTIPLE 0.50 | ROUNDSIG( 12345.27 , 0.5 ) | 12345.5000 | Number |
If you prefer a more functional syntax, you can use the ROUNDSIG, ROUNDSIGDOWN or ROUNSIGUP functions, all with 2 number parameters.
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.
Syntax
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.
When one or both values are a set, the '=' operator is translated to SUBSET OF:
"a" = ["a", "b"]
is equivalent to"a" SUBSET OF ["a","b"]
, which returns true["a", "b"] = "a"
is also equivalent to"a" SUBSET OF ["a","b"]
, note that the arguments are switched. Because we have a set and a single value, there is only one way to interpret this with theSUBSET OF
function.["a"] = ["a", "b"]
is equivalent to["a"] SUBSET OF ["a","b"]
, which returns true["a", "b"] = ["a"]
is equivalent to["a", "b"] SUBSET OF ["a"]
, which returns false. (note that if "set1 = set2" returns true, it does not mean that "set2 = set1" will also return true)
To test if two sets are equal you could use the expression:
L1 SUBSET OF L2 AND L2 SUBSET OF L1
Return type
- boolean
Examples
Expression | Result | Type |
---|---|---|
1 = 2 | FALSE | Boolean |
1.0 = 1.0 | TRUE | Boolean |
DATE( 2012 , 1 , 1 ) = TODAY | FALSE | Boolean |
5 + 5 = 10 | TRUE | Boolean |
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
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
Expression | Result | Type |
---|---|---|
1 != 2 | TRUE | Boolean |
1.0 != 1.0 | FALSE | Boolean |
DATE( 2012 , 1 , 1 ) != TODAY | TRUE | Boolean |
5 + 5 != 10 | FALSE | Boolean |
LESS
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
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
Expression | Result | Type |
---|---|---|
1 < 2 | TRUE | Boolean |
1.0 < 1.0 | FALSE | Boolean |
DATE( 2012 , 1 , 1 ) < TODAY | TRUE | Boolean |
2000 < YEAR( TODAY ) | TRUE | Boolean |
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
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
Expression | Result | Type |
---|---|---|
1 <= 2 | TRUE | Boolean |
1.0 <= 1.0 | TRUE | Boolean |
DATE( 2012 , 1 , 1 ) <= TODAY | TRUE | Boolean |
4000 <= YEAR( TODAY ) | FALSE | Boolean |
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
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
Expression | Result | Type |
---|---|---|
1 > 2 | FALSE | Boolean |
1.0 > 1.0 | FALSE | Boolean |
DATE( 2012 , 1 , 1 ) > TODAY | FALSE | Boolean |
4000 > YEAR( TODAY ) | TRUE | Boolean |
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.
Syntax
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
Expression | Result | Type |
---|---|---|
1 => 2 | FALSE | Boolean |
1.0 >= 1.0 | TRUE | Boolean |
DATE( 2012 , 1 , 1 ) >= TODAY | FALSE | Boolean |
4000 >= YEAR( TODAY ) | TRUE | Boolean |
ANNUITY
This function is able to directly compute an annuity for you. An annuity is a terminating "stream" of fixed payments, i.e., a collection of payments to be periodically received over a specified period of time.
It is computed using this formula:
where
- J = monthly payment
- i = rate of interest
- n = number of terms
- T = loan amount
Syntax
ANNUITY ( loan_amount , interest_rate , number_of_terms )
- loan_amount - The total currency amount that should be paid back.
- interest_rate - The interest rate as type percentage that should be paid on a loan (As a fraction, e.g. 5% = 0.05)
- number_of_terms - The number of terms as integer.
Return type
- number - The monthly payment.
Examples
Expression | Result | Type |
---|---|---|
ANNUITY ( 1000 , 0.05 , 12 ) | 112.8254 | Number |
ANNUITY ( 1000 , 0.05 , 1 ) | 1050 | Number |