Page History

Table of Contents

minLevel	2

Panel

bgColor	white

ADD

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

Include Page

	_nav_BackToTop
	_nav_BackToTop

Panel

bgColor	white

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.

Person.name	Person.gender	Person.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

Include Page

	_nav_BackToTop
	_nav_BackToTop

Panel

bgColor	white

SUBTRACT

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

Syntax

Code Block
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

Include Page

	_nav_BackToTop
	_nav_BackToTop

Panel

bgColor	white

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

DIV

Include Page

	_nav_BackToTop
	_nav_BackToTop

Panel

bgColor	white

DIV

This function returns the integer portion of a division.

Syntax

Code Block
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

Include Page

	_nav_BackToTop
	_nav_BackToTop

Panel

bgColor	white

MOD

This function returns 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

Include Page

	_nav_BackToTop
	_nav_BackToTop

Panel

bgColor	white

ROUND

The ROUND function rounds 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

UI Text Box

type	note

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
ROUND 12345.23 UP TO POSITION -5 = 100000.0000

...

Include Page

	_nav_BackToTop
	_nav_BackToTop

Panel

bgColor	white

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
ROUND 12345.27 TO MULTIPLE 0.50 = 12345.5000 of type number

UI Text Box

type	note

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

Panel

bgColor	white

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

Include Page

	_nav_BackToTop
	_nav_BackToTop

Panel

bgColor	white

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.

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

...

Include Page

	_nav_BackToTop
	_nav_BackToTop

Panel

bgColor	white

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

Include Page

	_nav_BackToTop
	_nav_BackToTop

Panel

bgColor	white

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

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

Include Page

	_nav_BackToTop
	_nav_BackToTop

Panel

bgColor	white

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 TRUE
1.0 <= 1.0 returns TRUE
DATE(2012,1,1) <= TODAY returns TRUE
4000 <= YEAR(TODAY) returns FALSE

Include Page

	_nav_BackToTop
	_nav_BackToTop

Panel

bgColor	white

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 FALSE
DATE(2012,1,1) > TODAY returns FALSE
4000 > YEAR(TODAY) returns TRUE

Include Page

	_nav_BackToTop
	_nav_BackToTop

Panel

bgColor	white

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

boolean

Examples

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

...

4000 >= YEAR(TODAY) returns TRUE

Include Page

	_nav_BackToTop
	_nav_BackToTop

Panel

bgColor	white

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:

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

Include Page

	_nav_BackToTop
	_nav_BackToTop

Panel

bgColor	white

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

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

Include Page

	_nav_BackToTop
	_nav_BackToTop

Panel

bgColor	white

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:

$\text{[math]}$ Image Modified

where

J = monthly payment
i = rate of interest
n = number of terms
T = loan amount

Syntax

Code Block
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

Include Page

	_nav_BackToTop
	_nav_BackToTop

Space shortcuts

Page tree

Versions Compared

Old Version 15

New Version 16

Key

ADD

SUM

SUBTRACT

DIVIDE

DIV

DIV

MOD

ROUND

ROUND TO MULTIPLE

MULTIPLY

PRODUCT

POWER

POWER

LESS

LESS OR EQUAL

GREATER

GREATER OR EQUAL

EQUAL

NOT EQUAL

ANNUITY