...
| Panel |
|---|
|
ADDUse the '+' (plus) sign to add values of some type of number. Syntax | Code Block |
|---|
number1 + number2 + ... |
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. Attribute : | Entitiy.attribute | Value | Type |
|---|
| Child.pocketMoney |
| Expression | Result | Type |
|---|
| Child.pocketMoney + 3.00 |
| Currency | | Child.pocketMoney + 3 |
| Number | | 100 + Bonus.standard |
| Percentage | | Child.pocketMoney + Bonus.standard |
| Include Page |
|---|
| _nav_BackToTop |
|---|
| _nav_BackToTop |
|---|
|
|
| Panel |
|---|
|
SUMThis function adds the values of a collection of numbers. Syntax | Code Block |
|---|
SUM ( collection ) |
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 |
| Type |
|---|
| SUM ( COLLECT Person.age FROM ALL Person ) |
| 152 | Integer | | SUM ( COLLECT Person.age FROM ALL Person WHERE ( Person.gender = |
| 96 | Integer | | SUM ( [ 1 , 2 , 3 , 4 , 5 , 6 , 7 , 8 , 9 , 10 ] ) |
| Include Page |
|---|
| _nav_BackToTop |
|---|
| _nav_BackToTop |
|---|
|
|
| Panel |
|---|
|
SUBTRACTUse the '-' (minus) sign to subtract values of some type of number. Syntax | Code Block |
|---|
number1 - number2 - ... |
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 - currency - percentage = error
- percentage - currency = error
Examples Suppose the Suppose the following data model. Attribute . | Entitiy.attribute | Value | Type |
|---|
| Child.pocketMoney |
| Expression | Result | Type |
|---|
| Child.pocketMoney - 1.00 |
| Currency | | Child.pocketMoney - 3 |
| Number | | 100 - Bonus.standard |
| Percentage | | Child.pocketMoney - Bonus.standard |
| Include Page |
|---|
| _nav_BackToTop |
|---|
| _nav_BackToTop |
|---|
|
|
| Panel |
|---|
|
DIVIDEMULTIPLYUse the '/*' (forward slashasterisk) sign to divide multiply values of some type of number. Syntax | Code Block |
|---|
number1 /* number2 /* ... |
number1 - DividendFirst factor; number or attribute of some type of number to be divided by other number values. number2 - DivisorSecond factor; 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. Attribute . | Entitiy.attribute | Value | Type |
|---|
| Child.pocketMoney |
| Expression | Value | Type |
|---|
| Child.pocketMoney |
| Currency | | Child.pocketMoney |
| 15.0000 | Number | | Bonus.standard |
| Include Page |
|---|
| _nav_BackToTop |
|---|
| _nav_BackToTop |
|---|
|
|
| Panel |
|---|
|
DIVPRODUCTThis function returns the product of the integer portion values of a divisioncollection of numbers. Syntax | Code Block |
|---|
number1 PRODUCT ( DIVcollection number2) |
Return type Return types number integer currency percentageinteger
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
| Include Page |
|---|
_nav_BackToTop | _nav_BackToTop | . | 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 |
| Include Page |
|---|
| _nav_BackToTop |
|---|
| _nav_BackToTop |
|---|
|
|
| Panel |
|---|
|
DIVIDEUse the '/' (forward slash) sign to divide values of some type of number |
| Panel |
|---|
|
MODThis function returns the remainder of a division. Syntax
| Code Block |
|---|
number1 %/ number2 / ... |
Return types number % / number = number integer % / integer = integernumber currency % / currency = currencynumber percentage % / percentage = percentagenumber 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. Attribute . | Entitiy.attribute | Value | Type |
|---|
| Child.pocketMoney |
| Expression | Result | Type |
|---|
| Child.pocketMoney |
| / 1.00 | 2.50 | Currency | | Child.pocketMoney |
Bonus.standard % 3 = 2.00 of type percentage 9 % | .25 | Currency | | 5.00 / 3 | 1.6667 | Number | | Bonus.standard / 5 | 1.00 | Percentage |
2.50 = 1.5000 of type number| Include Page |
|---|
| _nav_BackToTop |
|---|
| _nav_BackToTop |
|---|
|
|
| Panel |
|---|
|
ROUNDDIVThe ROUND function rounds a number value to a specified number of decimal placesThis function returns the integer portion of a division. Syntax | Code Block |
|---|
ROUND number1 number [UP | DOWN] TO POSITION positionDIV number2 |
number1 - Dividend; number number - Number or attribute of some type of number to be roundeddivided by another number value. UP or DOWNnumber2 - 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
| UI Text Box |
|---|
| 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 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 Return type 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 |
ROUND 12345.23 UP TO POSITION -5 = 100000.0000| Include Page |
|---|
| _nav_BackToTop |
|---|
| _nav_BackToTop |
|---|
|
|
| Panel |
|---|
|
MODThis function returns the remainder of a division ROUND TO MULTIPLEThe ROUND TO MULTIPLE function rounds a number value to a specified multiple. Syntax | Code Block |
|---|
ROUND number1 number [UP | DOWN] TO MULTIPLE multiple% number2 |
number1 - Dividend; number number - Number or attribute of some type of number to be roundeddivided by another number value. UP or DOWNnumber2 - 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 - ROUND 12345.27 TO MULTIPLE 0.50 = 12345.5000 of type number
| UI Text Box |
|---|
| If you prefer a more functional syntax, you can use the ROUNDSIG, ROUNDSIGDOWN or ROUNSIGUP functions, all with 2 number parameters. |
| Include Page |
|---|
_nav_BackToTop | _nav_BackToTop | |
...
MULTIPLY
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 |
| Include Page |
|---|
| _nav_BackToTop |
|---|
| _nav_BackToTop |
|---|
|
|
| Panel |
|---|
|
POWERUse the '**' operator to raise a number to a power. Syntax | Code Block |
|---|
base ** exponent |
|
Use the '*' (asterisk) sign to multiply values of some type of number.
Syntax
| Code Block |
|---|
number1 * number2 * ... |
...
...
...
...
...
...
* 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 |
|
...
...
...
...
| Expression | Result | Type |
|---|
| Child.pocketMoney ** 1.00 |
|
...
...
| Currency | | Child.pocketMoney ** 2 |
|
...
...
...
| Number | | Bonus.standard ** 5 |
|
...
...
| Include Page |
|---|
| _nav_BackToTop |
|---|
| _nav_BackToTop |
|---|
|
|
...
...
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
...
The ROUND function rounds a number value to a specified number of decimal places. Syntax |
...
PRODUCT(collection)
collection - A collection of number values.
Return types
number
integer
currency
percentage
Examples
Suppose the following data model.
...
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
| UI Text Box |
|---|
| 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 |
|
LESS
...
ROUND TO MULTIPLEThe ROUND TO MULTIPLE function rounds a number value to a specified multiple. Syntax |
...
value1 < value2
...
ROUND number [UP | DOWN] TO MULTIPLE multiple |
|
...
...
...
...
Return type
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 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 |
| UI Text Box |
|---|
| If you prefer a more functional syntax, you can use the ROUNDSIG, ROUNDSIGDOWN or ROUNSIGUP functions, all with 2 number parameters. |
| Include Page |
|---|
| _nav_BackToTop |
|---|
| _nav_BackToTop |
|---|
|
|
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
Examples
1 <= 2 returns TRUE
1.0 <= 1.0 returns TRUE
DATE(2012,1,1) <= TODAY returns TRUE
4000 <= YEAR(TODAY) returns FALSE
...
...
...
=' compares two values and returns the boolean value TRUE if the left value is |
...
equal to the right value and FALSE otherwise. Syntax |
...
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
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
| 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
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
| 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.
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:
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: | Code Block |
|---|
L1 SUBSET OF L2 AND L2 SUBSET OF L1 |
Return type 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 |
| Include Page |
|---|
| _nav_BackToTop |
|---|
| _nav_BackToTop |
|---|
|
|
| Panel |
|---|
|
NOT EQUALThe 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 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 |
| Include Page |
|---|
| _nav_BackToTop |
|---|
| _nav_BackToTop |
|---|
|
|
| Panel |
|---|
|
LESSThe 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 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 |
| Include Page |
|---|
| _nav_BackToTop |
|---|
| _nav_BackToTop |
|---|
|
|
| Panel |
|---|
|
LESS OR EQUALThe 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 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 |
| Include Page |
|---|
| _nav_BackToTop |
|---|
| _nav_BackToTop |
|---|
|
|
| Panel |
|---|
|
GREATERThe 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 Examples | Expression | Result | Type |
|---|
| 1 > 2 | FALSE | Boolean | | 1.0 > 1.0 | FALSE | Boolean |
|
| Code Block |
|---|
L1 SUBSET OF L2 AND L2 SUBSET OF L1 |
Return type
Examples
...
1 = 2 returns FALSE
...
...
...
| FALSE | Boolean | | 4000 > YEAR( TODAY ) | TRUE | Boolean |
| Include Page |
|---|
| _nav_BackToTop |
|---|
| _nav_BackToTop |
|---|
|
|
...
...
>=' 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 - 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 Examples |
...
...
...
...
| TRUE | Boolean | | DATE( 2012 , 1 , 1 ) |
|
...
| >= TODAY | FALSE | Boolean | | 4000 >= YEAR( TODAY ) | TRUE | Boolean |
| Include Page |
|---|
| _nav_BackToTop |
|---|
| _nav_BackToTop |
|---|
|
|
| Panel |
|---|
|
ANNUITYThis 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 | Code Block |
|---|
ANNUITY ( [currency], [percentage], [integer]) loan_amount , interest_rate , number_of_terms ) |
loan_amount - The total currency currency - The total amount that should be payed paid back. percentage interest_rate - The interest rate as type percentage that should be paid on a loan (As a fraction, e.g. 5% = 0.05) - integer number_of_terms - The amount number of terms as integer.
Return type Examples | Expression | Result | Type |
|---|
| ANNUITY ( 1000 , 0.05 , 12 ) |
| 112.8254 | Number | | ANNUITY ( 1000 , 0.05 , 1 ) |
| Include Page |
|---|
| _nav_BackToTop |
|---|
| _nav_BackToTop |
|---|
|
|