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

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

FunctionDescription
ADD (+)Use the '+' (plus) sign to add values of some type of number.
SUBTRACT (-)Use the '-' (minus) sign to subtract values of some type of number.
MULTIPLY (*)Use the '*' (asterisk) sign to multiply values of some type of number.
DIVIDE (/)Use the '/' (forward slash) sign to divide values of some type of number.
SUMAdds the values of a collection of numbers.
PRODUCTReturns the product of the values of a collection of numbers.
DIVReturns the integer portion of a division.
MODReturns the remainder of a division.
POWER (**)Use the '**' operator to raise a number to a power.
LOGCalculates the logarithm of a number to a base.
ROUNDRounds a number value to a specified number of decimal places.
ROUND DOWNRounds a number value down to a specified number of decimal places.
ROUND UPRounds a number value up to a specified number of decimal places.
ROUND TO MULTIPLERounds a number value to a specified multiple.
ROUND UP TO MULTIPLERounds a number value up to a specified multiple.
ROUND DOWN TO MULTIPLERounds a number value down to a specified multiple.
ROUNDSIGAlternative syntax for ROUND TO MULTIPLE. 
ROUNDSIGUPAlternative syntax for ROUND UP TO MULTIPLE. 
ROUNDSIGDOWNAlternative 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 '!=' compares two values and returns the boolean value TRUE if the left value is not equal to the right value and FALSE otherwise.
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.
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.
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.
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.
ANNUITYComputes an annuity. An annuity is a terminating "stream" of fixed payments. 

Functions

ADD (+)


Use the '+' (plus) sign to add values of some type of number.


Syntax

number1 + number2 + ...


Inputs

  • 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:

Entity.attributeValueType
Child.pocketMoney2.50Currency
Bonus.standard5.00Percentage
ExpressionResultType
Child.pocketMoney + 3.005.50Currency
Child.pocketMoney + 35.50Currency
2.50 + 35.5000Number
100 + Bonus.standard105.00Percentage
Child.pocketMoney + Bonus.standardError


 

Back to Top


SUBTRACT (-)


Use the '-' (minus) sign to subtract values of some type of number.


Syntax

 number1 - number2 - ...


Inputs

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

Entity.attributeValueType
Child.pocketMoney2.50Currency
Bonus.standard5.00Percentage
ExpressionResultType
Child.pocketMoney - 1.001.50Currency
Child.pocketMoney - 3-0.50Currency
5.00 - 32.0000Number
100 - Bonus.standard95.00Percentage
Child.pocketMoney - Bonus.standardError


 

Back to Top


MULTIPLY (*)


Use the '*' (asterisk) sign to multiply values of some type of number.


Syntax

 number1 * number2 * ...


Inputs

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

Entity.attributeValueType
Child.pocketMoney2.50Currency
Bonus.standard5.00Percentage
ExpressionValueType
Child.pocketMoney * 1.002.50Currency
Child.pocketMoney * 25.00Currency
5.00 * 315.0000Number
Bonus.standard * 525.00Percentage


 

Back to Top


DIVIDE (/)


Use the '/' (forward slash) sign to divide values of some type of number.


Syntax

 number1 / number2 / ...


Inputs

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

Entity.attributeValueType
Child.pocketMoney2.50Currency
Bonus.standard5.00Percentage
ExpressionResultType
Child.pocketMoney / 1.002.50Currency
Child.pocketMoney / 21.25Currency
5.00 / 31.6667Number
Bonus.standard / 51.00Percentage


 

Back to Top


SUM


Adds the values of a collection of numbers.


Syntax

 SUM ( collection )


Inputs

  • 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
ExpressionResultType
SUM ( COLLECT Person.age FROM ALL Person )152Integer
SUM ( COLLECT Person.age FROM ALL Person WHERE ( Person.gender = "m" ) )96Integer
SUM ( [ 1 , 2 , 3 , 4 , 5 , 6 , 7 , 8 , 9 , 10 ] )55Integer


 

Back to Top


PRODUCT


Returns the product of the values of a collection of numbers.


Syntax

 PRODUCT ( collection )


Input

  • 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
ExpressionValueType
PRODUCT ( COLLECT Person.score FROM ALL Person )10327.5000Number
PRODUCT ( COLLECT Person.score FROM ALL Person WHERE ( Person.score < 8 ) )135.0000Number
PRODUCT ( [ 3.5 , 2.2 ] )7.7000Number


 

Back to Top


DIV


Returns the integer portion of a division.


Syntax

 number1 DIV number2


Inputs

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

Entity.attributeValueType
Child.pocketMoney2.50Currency
Bonus.standard5.00Percentage
ExpressionResultType
Child.pocketMoney DIV 1.002Integer
Bonus.standard DIV 51Integer
6 DIV 4.501Integer


 

Back to Top


MOD


Returns the remainder of a division.


Syntax

 number1 % number2


Inputs

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

Entity.attributeValueType
Child.pocketMoney2.50Currency
Bonus.standard5.00Percentage
ExpressionResultType
Child.pocketMoney % 0.200.10Currency
Child.pocketMoney % 10.50Currency
Bonus.standard % 32.00Percentage
9 % 2.501.5000Number


 

Back to Top


POWER (**)


Use the '**' operator to raise a number to a power.


Syntax

 base ** exponent


Inputs

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

Entity.attributeValueType
Child.pocketMoney2.50Currency
Bonus.standard5.00Percentage
ExpressionResultType
Child.pocketMoney ** 1.002.50Currency
Child.pocketMoney ** 26.25Currency
5.00 ** 3125.0000Number
Bonus.standard ** 53125.00Percentage


 

Back to Top


LOG


Since 12.8

Calculates the logarithm of a number to a base.


Syntax

 LOG([number], [base])


Inputs

  • number- Number; number or attribute of some type of number.

  • base - Base; number or attribute of some type of number.


Return types

  • number


Examples

ExpressionResultType
LOG(100, 10)2.0Number
LOG(32, 2)5.0Number
LOG(3, 9)0.5Number


 

Back to Top


ROUND

ROUND DOWN

ROUND UP


Rounds a number value to a specified number of decimal places.


Syntax

 ROUND number [UP | DOWN] TO POSITION position


Inputs

  • 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

ExpressionAlternative syntaxResultType
ROUND 12345.23 TO POSITION -2ROUNDSIG( 12345.23, 100 )12300.0000Number
ROUND 12345.23 TO POSITION 1 ROUNDSIG( 12345.23 , 0.1 )12345.2000Number
ROUND 12345.23 UP TO POSITION -2ROUNDUP( 12345.23 , -2 )12400Integer
ROUND 12345.23 UP TO POSITION 1ROUNDUP( 12345.23 , 1 )12345.3000Number
ROUND 12345.23 DOWN TO POSITION -2ROUNDDOWN( 12345.23 , -2 )12300.0000Number
ROUND 12345.23 DOWN TO POSITION 1ROUNDDOWN( 12345.23 , 1 )12345.2000Number
ROUND 12345.23 UP TO POSITION -5ROUNDUP( 12345.23 , -5 )100000.0000Number


 

Back to Top


ROUND TO MULTIPLE

ROUND UP TO MULTIPLE

ROUND DOWN TO MULTIPLE

ROUNDSIG

ROUNDSIGUP

ROUNDSIGDOWN


Rounds a number value to a specified multiple.


Syntax

 ROUND number [UP | DOWN] TO MULTIPLE multiple


Inputs

  • 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

ExpressionAlternative syntaxResultType
ROUND 12345.23 TO MULTIPLE 500ROUNDSIG( 12345.23, 500 )12500.0000

Number

ROUND 12245.23 TO MULTIPLE 500ROUNDSIG( 12245.23, 500 )12000.0000Number
ROUND 12345.23 TO MULTIPLE 5 ROUNDSIG( 12345.23 , 5 )12345.0000Number
ROUND 12348.23 TO MULTIPLE 5ROUNDSIG( 12348.23 , 5 )

12350.0000

Number
ROUND 12345.23 TO MULTIPLE 0.5ROUNDSIG( 12345.23 , 0.5 )12345.0000Number
ROUND 12345.27 TO MULTIPLE 0.5ROUNDSIG( 12345.27 , 0.5 )12345.5000Number

If you prefer a more functional syntax, you can use the ROUNDSIG, ROUNDSIGDOWN or ROUNDSIGUP functions, all with 2 number parameters.


 

Back to Top


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


Inputs

  • 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 the SUBSET 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

ExpressionResultType
1 = 2FALSEBoolean

1.0 = 1.0

TRUEBoolean
DATE( 2012 , 1 , 1 ) = TODAYFALSEBoolean
5 + 5 = 10TRUEBoolean


 

Back to Top


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


Inputs

  • 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

ExpressionResultType
1 != 2TRUEboolean
1.0 != 1.0FALSEboolean
DATE( 2012 , 1 , 1 ) != TODAYTRUEboolean
5 + 5 != 10FALSEboolean


 

Back to Top


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


Inputs

  • 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

ExpressionResultType
1 < 2TRUEboolean
1.0 < 1.0FALSEboolean
DATE( 2012 , 1 , 1 ) < TODAYTRUEboolean
2000 < YEAR( TODAY )TRUEboolean


 

Back to Top


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


Inputs

  • 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

ExpressionResultType
1 <= 2TRUEboolean
1.0 <= 1.0TRUEboolean
DATE( 2012 , 1 , 1 ) <= TODAYTRUEboolean
4000 <= YEAR( TODAY )FALSEboolean


 

Back to Top


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


Inputs

  • 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

ExpressionResultType
1 > 2FALSEboolean
1.0 > 1.0FALSEboolean
DATE( 2012 , 1 , 1 ) > TODAYFALSEboolean
4000 > YEAR( TODAY )TRUEboolean


 

Back to Top


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


Inputs

  • 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

ExpressionResultType
1 => 2FALSEboolean
1.0 >= 1.0TRUEboolean
DATE( 2012 , 1 , 1 ) >= TODAYFALSEboolean
4000 >= YEAR( TODAY )TRUEboolean


 

Back to Top


ANNUITY


Computes an annuity. 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 )


Inputs

  • 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

ExpressionResultType
ANNUITY ( 1000 , 0.05 , 12 )112.8254Number
ANNUITY ( 1000 , 0.05 , 1 )1050Number


 

Back to Top


1 Comment

  1. Louis Wouters

    I noticed an issue with the DIV-function.


    Microsoft Excel's QUOTIENT-function has the exact same description ("Returns the integer portion of a division"),

    However both give different results:


    XYX DIV YQUOTIENT( X ; Y )
    000#DIV/0!
    309223372036854775807#DIV/0!
    37-10-4-3
    -3710-4-3

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