You are viewing the documentation for Blueriq 17. Documentation for other versions is available in our documentation directory.

You are viewing an old version of this page. View the current version.

Compare with Current View Page History

« Previous Version 4 Next »


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

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

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.namePerson.genderPerson.age
“Kim”“f”23
“Rick”“m”35
“Bob”“m”42
“John”“m”19
“Mary”“f”33

 

  • 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 '-' (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

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

 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

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.

  • 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

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. 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

ROUND

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

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 TO MULTIPLE

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

  • 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

 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

 PRODUCT(collection)
  • collection - A collection of number values.

Return types

  • number
  • integer
  • currency
  • percentage

Examples

Suppose the following data model.

 

Person.namePerson.score
“Kim”9
“Rick”8.5
“Bob”3
“John”7.5
“Mary”6

 

  • 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

 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

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

  • 1 < 2 returns TRUE
  • 1.0 < 1.0 returns FALSE
  • DATE(2012,1,1) < TODAY returns TRUE
  • 2000 < YEAR(TODAY) returns TRUE

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

  • 1 <= 2 returns TRUE
  • 1.0 <= 1.0 returns TRUE
  • DATE(2012,1,1) <= TODAY returns TRUE
  • 4000 <= YEAR(TODAY) returns FALSE

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

  • 1 > 2 returns FALSE
  • 1.0 > 1.0 returns FALSE
  • 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.

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

  • 1 >= 2 returns FALSE
  • 1.0 >= 1.0 returns TRUE
  • DATE(2012,1,1) >= TODAY returns FALSE
  • 4000 >= YEAR(TODAY) returns TRUE

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.

For value list, the '=' operator is implemented as SUBSET OF. To test if two value lists are equal you could use the expression:

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

 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
  • 5 + 5 != 10 returns FALSE

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( [currency], [percentage], [integer])
  • currency - The total amount that should be payed back.
  • percentage - The percentage that should be paid on a loan (As a fraction, e.g. 5% = 0.05)
  • integer - The amount of terms.

Return type

  • number - The monthly payment.

Examples

  • ANNUITY(1000, 0.05, 12) = 112.8254
  • ANNUITY(1000, 0.05, 1) = 1050

 

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

  • 1 != 2 returns TRUE
  • 1.0 != 1.0 returns FALSE
  • DATE(2012,1,1) != TODAY returns TRUE
  • 5 + 5 != 10 returns FALSE
  • No labels

Didn't find the answer you were looking for? Try the advanced search!