Versions Compared

Old Version 19

changes.mady.by.user Unknown User (j.lomas)

Saved on

compared with

New Version 20

changes.mady.by.user Munish Mahabiersing

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

Table of Contents
minLevel2


 

Panel
bgColorwhite

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
  • currency + percentage = error

Examples

Suppose the following data model:

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

...



Include Page
_nav_BackToTop
_nav_BackToTop

Panel
bgColorwhite

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

...


Include Page
_nav_BackToTop
_nav_BackToTop

 

Panel
bgColorwhite

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
  • currency - percentage = error
  • percentage - currency = error

Examples

Suppose the following data model.

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

...



Include Page
_nav_BackToTop
_nav_BackToTop

Panel
bgColorwhite

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
  • currency * percentage = currency

Examples

Suppose the following data model.

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

...


Include Page
_nav_BackToTop
_nav_BackToTop

Panel
bgColorwhite

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

 

 



Include Page
_nav_BackToTop
_nav_BackToTop

Panel
bgColorwhite

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
  • currency / percentage = currency

Examples

Suppose the following data model.

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

...


Include Page
_nav_BackToTop
_nav_BackToTop

Panel
bgColorwhite

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.

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

...


Include Page
_nav_BackToTop
_nav_BackToTop

Panel
bgColorwhite

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.

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

...


Include Page
_nav_BackToTop
_nav_BackToTop

Panel
bgColorwhite

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.

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


Include Page
_nav_BackToTop
_nav_BackToTop

...

 

Panel
bgColorwhite

LOG

Use the 'LOG' function to calculate the logarithm of a number to a base.

Syntax

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

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


Include Page
_nav_BackToTop
_nav_BackToTop

Panel
bgColorwhite

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
typenote

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

...


Include Page
_nav_BackToTop
_nav_BackToTop

Panel
bgColorwhite

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

ExpressionAlternative syntaxResultType
ROUND 12345.23 TO MULTIPLE 500ROUNDSIG( 12345.23, 500 )12500.0000Number
ROUND 12345.23 TO MULTIPLE 5 ROUNDSIG( 12345.23 , 5 )12345.0000Number
ROUND 12345.23 TO MULTIPLE 0.50ROUNDSIG( 12345.23 , 0.50 )12345.0000Number
ROUND 12345.27 TO MULTIPLE 0.50ROUNDSIG( 12345.27 , 0.5 )12345.5000Number
UI Text Box
typenote

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
bgColorwhite

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

ExpressionResultType
1 = 2FALSEBoolean

1.0 = 1.0

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

 


Include Page
_nav_BackToTop
_nav_BackToTop

Panel
bgColorwhite

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

ExpressionResultType
1 != 2TRUEBoolean
1.0 != 1.0FALSEBoolean
DATE( 2012 , 1 , 1 ) != TODAYTRUEBoolean
5 + 5 != 10FALSEBoolean
 


Include Page
_nav_BackToTop
_nav_BackToTop

Panel
bgColorwhite

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

ExpressionResultType
1 < 2TRUEBoolean
1.0 < 1.0FALSEBoolean
DATE( 2012 , 1 , 1 ) < TODAYTRUEBoolean
2000 < YEAR( TODAY )TRUEBoolean
 


Include Page
_nav_BackToTop
_nav_BackToTop

...

Panel
bgColorwhite

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

ExpressionResultType
1 > 2FALSEBoolean
1.0 > 1.0FALSEBoolean
DATE( 2012 , 1 , 1 ) > TODAYFALSEBoolean
4000 > YEAR( TODAY )TRUEBoolean
 


Include Page
_nav_BackToTop
_nav_BackToTop

Panel
bgColorwhite

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

ExpressionResultType
1 => 2FALSEBoolean
1.0 >= 1.0TRUEBoolean
DATE( 2012 , 1 , 1 ) >= TODAYFALSEBoolean
4000 >= YEAR( TODAY )TRUEBoolean
 


Include Page
_nav_BackToTop
_nav_BackToTop

 

...



Panel
bgColorwhite

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

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

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


Include Page
_nav_BackToTop
_nav_BackToTop