Formula Editor
The FintechOS Formula Editor is a complex tool found in the Studio. It is the place where a user writes an actual mathematical expressions and calculations. The FintechOS Formula Editor is found at the bottom of the “Add/Edit formula step” form.
For example, based on what a client inserts as data in his/hers KYC, the editor takes that information and uses the calculation formula inserted into the Editor to return data such as the net income of a loan applicant or the age limit for a contract or the insurance premium for a policy using complex arguments and functions. It is possible to test the formula inserted here by running a test. For more information about running a test, see Define Formula Expressions.
Syntax
In the Editor, a user inserts the formula that is built based on a given mathematical syntax whose structure is made of the formula body and the call for calculation which is the actual result. Intellisense is available for quick selection of definitions, with the possibility to get more info (such as properties and attributes) about the selected item.
When writing the formula, make sure that each formula matches to the step you are working on because the result may be used as input in the following steps.
Use the following syntax in the formula editor to define a formula output:
result = <formula expression>;
For formulas that return collections, use the following syntax (make sure that the formula expression includes an iteration argument):
result[i] = < ... iterationArgument[i] ... >;
For recursive formulas, define a simple type whole number formula and include a whole number argument in the Number of Iterations field (see Define Formula Expressions for details). For example, to generate a collection with the first x numbers in Fibonacci sequence, use a whole number input argument called x for the number of iterations (make sure x is greater than or equal to 2) and use the following formula expression:
result[0] = 1; result[1] =1; result[i] = result[i-1] + result[i-2];
When the formula body is complex, needs periodic update or must be simplified for transparency and traceability, we suggest you split it in separate steps, each with its own expression.
You can include multi-line C# code in the formula expression, as long as you assign a result value:
var a = 1;
var b = 2;
result = a + b;
The order of execution between operations such as addition “+”, substraction “-“, multiplication “*” and division “/” is respected and you can further control this by using parantheses () or by splitting the formula in steps.
In a formula receiving an argument data of a complex type with properties A,B,C of type string and D,E of type numeric, we can write:
var result = FROM(data).GROUPBY(["A","B"]).SUM("D");
The result will be a collection of items with properties A,B and C, where C will be the SUM for the group determined by the content of properties A and B
Pressing Ctrl+Space will launch the Intellisense that can help you learn more about the formula you are editing, keep track of parameters you’re typing, add calls to functions and various information with only a few keystrokes.
Formula can be of the following types:
- constant f = sum(1,n)
- linear f(x) = 2*x+1 , where x = 1,100
- 2-dimensional f(x,y) = x*y+30;
- n-dimensional etc.
- recursive f(x) = f(x-1)+20.
Already pre-defined Steps also appear in Intellisense since they can be called in any subsequent step.
Click the ''Save and Close'' button.
Formula Arguments
Primary formula arguments are defined in the formula input (see Define Formula Inputs). For example, if we have defined an argument called days in our formula input, we can create a formula step called years to convert the number of days into years with the following expression:
result = days / 365;
We can also use previous steps' outputs as arguments for subsequent step inputs. For example, after the above step, we can create a step that calculates an interest by multiplying the principal and rate input parameters with the years result from the previous step:
result = principal * rate * years
Built-in Functions
You can include the following built-in functions in your formula expressions:
Function | Description | Example |
Result |
---|---|---|---|
SUM(Vector) |
SUM function can be used to add all numbers in a range of cells. The arguments can be numbers, cells references or formula-driven numeric values. For instance, if you would like to calculate the sum of all items part of an array then you need to use an iteration formula. |
SUM([1, 2, 3]) |
6 |
ABS(Number) | It returns the absolute value of the number. | ABS(-3) |
3 |
POWER(Number, Exponent) | Calculates how many times to use the number in a multiplication. | POWER(10,3) | 1000 |
ODD(Number) | Specifies if the number is odd. | ODD(12) |
false |
EVEN(Number) | Specifies if the number is even. | EVEN(26) |
true |
TRUNC(Number) | Truncates the number to a specified number of decimal places. | TRUNC(17.51) |
17 |
ROUND(Number, Precision) | Rounds the number to the specified number of digits. | ROUND(17.51) |
18 |
ROUNDUP(Number) | Rounds the number upward to the specified number of digits. | ROUNDUP(-0.6) |
0 |
ROUNDDOWN(Number) | Rounds the number downward to the specified number of digits. | ROUNDDOWN(1.9) |
1 |
FLOOR(Number) | Receives one parameter and rounds the number down. | FLOOR(6.7) |
6 |
IIF(Condition, TrueExpression, FalseExpression) | IF function is a "conditional function" because it returns a value based on the condition that you specify. |
|
|
COUNT(Vector) | Returns the number of numerical values (numbers and dates) in the list of arguments. |
|
|
COUNTIF(Vector, FilterExpression) | Counts the number of cells within the range that meet the specified criteria. |
|
|
MIN(Vector) | Returns the minimal value from the list of arguments in a row. | MIN(1,2,3,5,6) |
1 |
MAX(Vector) | Returns the maximum value from the list of arguments in a row. | MAX(111,22,33,44,55) |
55 |
AVERAGE(Number1, Number2, Number3, …) | Returns the average of the arguments. | AVERAGE(1234,67543,5752) |
24843 |
AVERAGE(Vector) | Returns the average of the arguments in a row. | AVERAGE ([4643,652348,83284]) |
246758 |
SUMIF(Vector, FilterExpression) |
Adds up the cells in a specified range that meet a certain condition. SUMIF can evaluate only a single criteria. “it” is a mandatory term, standing for “item” of an array, for all Filter Expressions in all formulas which evaluate an expression. |
|
|
SELECT(Vector, TransformExpression) |
|
SELECT (IncomeList, it * Database (''IncomeAdjuster'')) |
|
RANGE(Vector, RangeOperators) | Navigate inside an array | RANGE([1,2,3,4,5,6],SKIP(2)]) |
VECTOR(3,4,5,6) |
SKIP(Number) |
Combined with RANGE function, you can skip an item of an array, in case for example you would like to add all items except the 3rd one in the array. |
|
|
TAKE(Number) | Combined with RANGE function, you can take as many array items as indicated by the input parameter. |
|
|
RATE (nper, pmt, pv, [fv], [type], [guess]) |
Returns the interest rate per period of an annuity. You can use RATE to calculate the periodic interest rate, then multiply as required to derive the annual interest rate. Arguments:
|
RATE(36, 150, -5000) |
0.42 |
PMT (rate, nper, pv, [fv], [type]) |
Calculates the payment for a loan based on constant payments and a constant interest rate. Arguments:
|
A 3-year (36 months) loan for 10000 USD at 8% interest rate: PMT(0.08/12, 36, 10000) |
-313.36 |
Examples
For Simple types
Returns one of two values, depending on whether the Boolean Condition evaluates to true or false.
Syntax
/**
* @param booleanCondition - condition that has to return true/false
* @param trueValue - the value that is returned if booleanCondition is evaluated as true
* @param falseValue - the value that is returned if booleanCondition is evaluated as false
* @returns - return trueValue or falseValue
*/
IIF(booleanCondition, trueValue, falseValue): boolean
Example
//Example
/*
Input:
simpleCollection = [100, 200, 300, 400]
Output:
result = 250
*/
result = IIF(COUNT(simpleCollection) > 0, SUM(simpleCollection)/COUNT(simpleCollection) , 0);
Returns the average of the numbers received as parameters.
Syntax
/**
* @param {number} number1 - required, the first number for which you want the average
* @param {number} number2, ... - optional, additional numbers for which you want the average
* @returns {number} - the average of the numbers
*/
AVERAGE(number1, [number2], ...): number
Example
/*
Input:
Output:
result = 250
*/
result = AVERAGE(100, 200, 300, 400);
Returns true if the integer number is odd, otherwise false.
Syntax
/**
* @param {number} number - the number the needs to be verified
* @returns {boolean} -
*/
ODD(number): boolean
Example
/*
Input:
num = 3
Output:
result = true
*/
result = ODD(num);
/*
Input:
num = 2
Output:
result = false
*/
result = ODD(num);
Returns true if the integer number is even, otherwise false.
Syntax
/**
* @param {number} number - the number the needs to be verified
* @returns {boolean} -
*/
EVEN(number): boolean
Example
/*
Input:
num = 3
Output:
result = false
*/
result = EVEN(num);
/*
Input:
num = 2
Output:
result = true
*/
result = EVEN(num);
Calculates the integral part of a specified decimal number.
Syntax
/**
* @param {number} number - the number the needs to be truncated
* @returns {number} - the integral part of the number
*/
TRUNC(number): number
Example
/*
Input:
num = 17.53M
Output:
result = 17
*/
result = TRUNC(num);
/*
Input:
num = -17.53M
Output:
result = 17
*/
result = TRUNC(num);
Rounds a decimal value to a specified number of fractional digits.
Syntax
/**
* @param {number} num - the number the needs to be rounded
* @param {number} precision - optional, number of decimal places in the return value. The default value is 0
* @returns {number} - The number nearest to num that contains a number of fractional digits equal to precision.
*/
ROUND(num, [precision]): number
Example
/*
Input:
num = -17.51M
Output:
result = -18
*/
result = ROUND(num);
/*
Input:
num = 17.51M
precision = 1
Output:
result = 17.5
*/
result = ROUND(num, precision);
Returns the smallest integral value that is greater than or equal to the specified decimal number.
Syntax
/**
* @param {number} num - the number the needs to be rounded up
* @returns {number} - the smallest integral value that is greater than or equal to the specified decimal number.
*/
ROUNDUP(num): number
Example
/*
Input:
num = -17.51M
Output:
result = -17
*/
result = ROUNDUP(num);
/*
Input:
num = 17.51M
Output:
result = 18
*/
result = ROUNDUP(num);
Returns the largest integer less than or equal to the specified decimal number.
Syntax
/**
* @param {number} num - the number the needs to be rounded down
* @returns {number} - the largest integer less than or equal to the specified decimal number.
*/
ROUNDDOWN(num): number
Example
/*
Input:
num = -17.51M
Output:
result = -18
*/
result = ROUNDDOWN(num);
/*
Input:
num = 17.51M
Output:
result = 17
*/
result = ROUNDDOWN(num);
Returns the largest integer less than or equal to the specified decimal number.
Syntax
/**
* @param {number} num - the number
* @returns {number} - the largest integer less than or equal to the specified decimal number.
*/
FLOOR(num): number
Example
/*
Input:
num = -17.51M
Output:
result = -18
*/
result = FLOOR(num);
/*
Input:
num = 17.51M
Output:
result = 17
*/
result = FLOOR(num);
For Collection types
Applies a function to each element of the collection and returns a new collection with the results of the function invocation.
//Example
/*
Input:
productCollection = [
{"name":"Product1", "priceWithVAT": 100 },
{"name":"Product2", "priceWithVAT": 200 }]
Output:
result = [100, 200]
*/
result = FROM(productCollection).SELECT(x=>x.priceWithVAT);
//Example
/*
Input:
simpleCollection = [100, 200, 300, 400]
Output:
result = [200, 400, 600, 800]
*/
result = FROM(simpleCollection).SELECT(x=>x * 2);
Applies a function to each element of the collection and returns a new collection with the filtered elements that respect the condition
//Example
/*
Input:
productCollection = [
{"name":"Product1", "priceWithVAT": 100 },
{"name":"Product2", "priceWithVAT": 200 }]
Output:
result = [200]
*/
result = FROM(productCollection).WHERE(x=>x.priceWithVAT>150).SELECT(x=>x.priceWithVAT);
Returns first element of the collection or the default value (0 for numeric elements). It goes well when used with WHERE and you are sure only one record is returned. ```javascript //Example /* Input: productCollection = [ {"name":"Product1", "priceWithVAT": 100 }, {"name":"Product2", "priceWithVAT": 200 }]
Output:
result = 200 */ result = FROM(productCollection).WHERE(x=>x.priceWithVAT>150).SELECT(x=>x.priceWithVAT).FIRSTORDEFAULT(),
### GROUPBY ###
**Can only be used with agregate function SUM**
Returns a new collection grouped by a property of the object.
```javascript
//Example
/*
Input:
productCollection = [
{category: "Cat1", name:"Product1", priceWithVAT: 100, quantity: 1 },
{category: "Cat2", name:"Product2", priceWithVAT: 200, quantity: 2 },
{category: "Cat1", name:"Product3", priceWithVAT: 300, quantity: 3 }]
Output:
result = [
{
"category": "Cat1",
"priceWithVAT": 400.0
},
{
"category": "Cat2",
"priceWithVAT": 200.0
}
]
*/
result = FROM(productCollection).GROUPBY("category").SUM("priceWithVAT");
Returns a new collection with a new property added to all elements in the collection. Also, for each element it assigns a value for the new added property.
//Example
/*
Input:
productCollection = [
{category: "Cat1", name:"Product1", priceWithVAT: 100, quantity: 1 },
{category: "Cat2", name:"Product2", priceWithVAT: 200, quantity: 2 },
{category: "Cat1", name:"Product3", priceWithVAT: 300, quantity: 3 }]
Output:
result = [
{
"category": "Cat1",
"name": "Product1",
"priceWithVAT": 100.0,
"quantity": 1.0,
"totalPriceWithVAT": 100.0
},
{
"category": "Cat2",
"name": "Product2",
"priceWithVAT": 200.0,
"quantity": 2.0,
"totalPriceWithVAT": 400.0
},
{
"category": "Cat1",
"name": "Product3",
"priceWithVAT": 300.0,
"quantity": 3.0,
"totalPriceWithVAT": 900.0
}
]
*/
result = FROM(productCollection).EXTENDELEMENTS("totalPriceWithVAT", x=>x.priceWithVAT * x.quantity);
Count is a property of collections, it returns the number of elements from the collection.
//Example
/*
Input:
productCollection = [
{"name":"Product1", "priceWithVAT": 100 },
{"name":"Product2", "priceWithVAT": 200 }]
Output:
result = 2
*/
result = FROM(productCollection).WHERE(x=>x.priceWithVAT>=100).Count;
For Simple Collections types
Generates a sequence of numbers within a specified range.
Syntax
/**
* @param {number[]} simpleCollection - simple collection of decimals
* @param rangeOperators - range operators of type SKIP and TAKE
* @returns {number[]} - the sequence of numbers within a specified range.
*/
RANGE(simpleCollection, rangeOperators...): number[]
Example
//Example
/*
Input:
simpleCollection = [100, 200, 300, 400]
Output:
result = [100, 200]
*/
result = RANGE(simpleCollection, TAKE(2));
//Example
/*
Input:
simpleCollection = [100, 200, 300, 400]
Output:
result = [200, 300]
*/
result = RANGE(simpleCollection, SKIP(1), TAKE(2));
//Example
/*
Input:
simpleCollection = [100, 200, 300, 400]
Output:
result = [200, 300, 400]
*/
result = RANGE(simpleCollection, SKIP(1));
Returns the min/max from the collection.
Syntax
/**
* @param {number[]} simpleCollection - simple collection of decimals
* @returns {number} -
*/
MIN(simpleCollection): number
MAX(simpleCollection): number
Example
//Example
/*
Input:
simpleCollection = [100, 200, 300, 400]
Output:
result = 100
*/
result = MIN(simpleCollection);
//Example
/*
Input:
simpleCollection = [100, 200, 300, 400]
Output:
result = 400
*/
result = MAX(simpleCollection);
Returns the average of the collection of numbers received as parameter
Syntax
/**
* @param {number[]} simpleCollection - simple collection of decimals
* @returns {number} - the average of the numbers
*/
AVERAGE(simpleCollection): number
Example
//Example
/*
Input:
simpleCollection = [100, 200, 300, 400]
Output:
result = 250
*/
result = AVERAGE(simpleCollection);
Returns the sum/count of the elements from the collection.
Syntax
/**
* @param {number[]} simpleCollection - simple collection of decimals
* @returns {number} -
*/
SUM(simpleCollection): number
COUNT(simpleCollection): number
Example
//Example
/*
Input:
simpleCollection = [100, 200, 300, 400]
Output:
result = 1000
*/
result = SUM(simpleCollection);
//Example
/*
Input:
simpleCollection = [100, 200, 300, 400]
Output:
result = 4
*/
result = COUNT(simpleCollection);
Returns the sum/count of the elements from the collection that respect the condition
Syntax
/**
* @param {number[]} simpleCollection - simple collection of decimals
* @param filter - the condition for filtering
* @returns {number} -
*/
SUMIF(simpleCollection, filter): number
COUNTIF(simpleCollection, filter): number
Example
The it from the filter is just a convention for naming an item of the collection.
//Example
/*
Input:
simpleCollection = [100, 200, 300, 400]
Output:
result = 700
*/
result = SUMIF(simpleCollection, it > 250);
//Example
/*
Input:
simpleCollection = [100, 200, 300, 400]
Output:
result = 2
*/
result = COUNTIF(simpleCollection, it > 250);
Formula Built-in Excel Functions
Returns the interest rate per period of an annuity. RATE is calculated by iteration and can have zero or more solutions.
Syntax
/**
* @param {number} nPer - Required. The total number of payment periods in an annuity.
* @param {number} pmt - Required. The payment made each period and cannot change over the life of the annuity.
* @param {number} pv - Required. The present value — the total amount that a series of future payments is worth now.
* @param {number} fv - Optional. The future value, or a cash balance you want to attain after the last payment is made. Default value 0.
* @param {number} dueDate - Optional. 0 or omitted - At the end of the period / 1 - At the beginning of the period
* @param {number} guess - Optional. Your guess for what the rate will be. Default is 10%
* @returns {number} - Returns the interest rate per period of an annuity
*/
RATE(nPer, pmt, pv, fv, dueDate, guess)): number
Example
//Example
/*
Input:
years: 4,
monthlyPayment: -200,
loanAmount: 8000
Output:
result = 0.00770147248821008M
*/
result = RATE(years * 12, monthlyPayment, loanAmount);
Calculates the payment for a loan based on constant payments and a constant interest rate.
Syntax
/**
* @param {number} rate - Required. The interest rate for the loan.
* @param {number} nPer - Required. The total number of payments for the loan.
* @param {number} pv - Required. The present value — the total amount that a series of future payments is worth now.
* @param {number} fv - Optional. The future value, or a cash balance you want to attain after the last payment is made. Default value 0.
* @param {number} dueDate - Optional. 0 or omitted - At the end of the period / 1 - At the beginning of the period
* @returns {number} - Returns the payment for a loan based on constant payments and a constant interest rate.
*/
PMT(rate, nPer, pv, fv, dueDate)): number
Example
//Example
/*
Input:
years: 4,
monthlyPayment: -200,
loanAmount: 8000
Output:
result = 0.00770147248821008M
*/
result = RATE(years * 12, monthlyPayment, loanAmount);
Formula Built-in DateTime Functions
Returns the datePart of the date. The datePart refers to DAY, MONTH Or YEAR. Use DATEPART.DAY/MONTH/YEAR to specify the desired format.
Syntax
/**
* @param dateTimeArgument {Date} - the date argument
* @param datePart - refers to DAY, MONTH Or YEAR
* @returns {number} - the datePart of the date
*/
GETDATEPART(dateTimeArgument, datePart): number
Example
//Example
/*
Input:
dateTimeArgument = "2021-05-01T00:00:00.000Z";
datePart = DATEPART.DAY;
Output:
result = 1;
*/
result = GETDATEPART(dateTimeArgument, datePart);
Returns the day of the month.
Syntax
Returns the date without the time component.
Syntax
/**
* @param dateTimeArgument {Date} - the date argument
* @returns {Date} - the date without the time component
*/
GETDATE(dateTimeArgument): Date
Example
/*
Input:
dateTimeArgument = "2021-05-01T10:05:00.000Z";
Output:
result = "2021-05-01T00:00:00Z";
*/
result = GETDATE(dateTimeArgument);
/**
* @param dateTimeArgument {Date} - the date argument
* @returns {number} - the day of the month
*/
GETDAY(dateTimeArgument): number
Example
/*
Input:
dateTimeArgument = "2021-05-15T00:00:00.000Z";
Output:
result = 15;
*/
result = GETDAY(dateTimeArgument);
Returns the month component of the date.
Syntax
/**
* @param dateTimeArgument {Date} - the date argument
* @returns {number} - the month component of the date
*/
GETMONTH(dateTimeArgument): number
Example
/*
Input:
dateTimeArgument = "2021-05-15T00:00:00.000Z";
Output:
result = 5;
*/
result = GETMONTH(dateTimeArgument);
Returns the year component of the date.
Syntax
/**
* @param dateTimeArgument {Date} - the date argument
* @returns {number} - the day of the month
*/
GETYEAR(dateTimeArgument): number
Example
/*
Input:
dateTimeArgument = "2021-05-15T00:00:00.000Z";
Output:
result = 2021;
*/
result = GETYEAR(dateTimeArgument);
Returns a new date that adds the specified number of datePart. The datePart refers to DAY, MONTH or YEAR. Use DATEPART.DAY/MONTH/YEAR to specify the desired format.
Syntax
/**
* @param dateTimeArgument {Date} - the date argument
* @param value {number} - the value to be added to the given date
* @param datePart - refers to DAY, MONTH Or YEAR
* @returns {Date} - a new date that adds the specified number of datePart
*/
ADDDATEPART(dateTimeArgument, value, datePart): Date
Example
/*
Input:
dateTimeArgument = "2021-05-15T00:00:00.000Z";
value = 5;
datePart = DATEPART.MONTH;
Output:
result = "2021-10-15T00:00:00.00Z";
*/
result = ADDDATEPART(dateTimeArgument, 5, DATEPART.MONTH);
Returns a new date that adds the specified number of days.
Syntax
/**
* @param dateTimeArgument {Date} - the date argument
* @param value {number} - the value to be added to the given date
* @returns {Date} - a new date that adds the specified number of days
*/
ADDDAYS(dateTimeArgument, value): Date
Example
/*
Input:
dateTimeArgument = "2021-05-15T00:00:00.000Z";
value = 15;
Output:
result = "2021-05-30T00:00:00.000Z";
*/
result = ADDDAYS(dateTimeArgument, value);
Returns a new date that adds the specified number of months.
Syntax
/**
* @param dateTimeArgument {Date} - the date argument
* @param value {number} - the value to be added to the given date
* @returns {Date} - a new date that adds the specified number of months
*/
ADDMONTHS(dateTimeArgument, value): Date
Example
/*
Input:
dateTimeArgument = "2021-05-15T00:00:00.000Z";
value = 2;
Output:
result = "2021-07-15T00:00:00.000Z";
*/
result = ADDMONTHS(dateTimeArgument, value);
Returns a new date that adds the specified number of years.
Syntax
/**
* @param dateTimeArgument {Date} - the date argument
* @param value {number} - the value to be added to the given date
* @returns {Date} - a new date that adds the specified number of years
*/
ADDYEARS(dateTimeArgument, value): Date
Example
/*
Input:
dateTimeArgument = "2021-05-15T00:00:00.000Z";
value = 5;
Output:
result = "2026-05-15T00:00:00.000Z";
*/
result = ADDYEARS(dateTimeArgument, value);
Returns '-1' if date1 is earlier than date2, '0' if date1 is the same as date2, and it returns '1' if date1 is later than date2.
Syntax
/**/**
* @param dateTimeArgument1 {Date} - the first date argument
* @param dateTimeArgument2 {Date} - the second date argument
* @returns {number} - '-1' if dateTimeArgument1 is earlier than dateTimeArgument2, '0' if dateTimeArgument1 is the same as dateTimeArgument2 and it returns '1' if dateTimeArgument1 is later than dateTimeArgument2
*/
DATECOMPARE(dateTimeArgument1, dateTimeArgument2): number
Example
/*/*
Input:
dateTimeArgument1 = "2021-05-15T00:00:00.000Z";
dateTimeArgument2 = "2021-06-15T00:00:00.000Z";
Output:
result = -1;
*/
result = DATECOMPARE(dateTimeArgument1, dateTimeArgument2);
Returns the difference between two dates. The difference is expressed in the specified datePart. The datePart refers to DAY, MONTH or YEAR. Use DATEPART.DAY/MONTH/YEAR to specify the desired format.
Syntax
/**/**
/**
* @param dateTimeArgument1 {Date} - the first date argument
* @param dateTimeArgument2 {Date} - the second date argument
* @param datePart - refers to DAY, MONTH Or YEAR
* @returns {number} - the difference between dateTimeArgument1 and dateTimeArgument2 expressed in the specified datePart
*/
DATEDIFF(dateTimeArgument1, dateTimeArgument2, datePart): number
Example
/*
Input:
dateTimeArgument1 = "2021-05-25T00:00:00.000Z";
dateTimeArgument2 = "2021-05-05T00:00:00.000Z";
datePart = DATEPART.DAY;
Output:
result = 20;
*/
result = DATEDIFF(dateTimeArgument1, dateTimeArgument2, datePart);
Returns the difference of days between two dates.
Syntax
/**
* @param dateTimeArgument1 {Date} - the first date argument
* @param dateTimeArgument2 {Date} - the second date argument
* @returns {number} - the difference of days between two dates
*/
DIFFDAYS(dateTimeArgument1, dateTimeArgument2): number
Example
/*
Input:
dateTimeArgument1 = "2021-05-01T00:00:00.000Z";
dateTimeArgument2 = "2021-05-11T00:00:00.000Z";
Output:
result = -10;
*/
result = DIFFDAYS(dateTimeArgument1, dateTimeArgument2);
Returns the difference between two dates. The difference is expressed in the specified datePart. The datePart refers to DAY, MONTH or YEAR. Use DATEPART.DAY/MONTH/YEAR to specify the desired format.
Syntax
/**/**
/**
* @param dateTimeArgument1 {Date} - the first date argument
* @param dateTimeArgument2 {Date} - the second date argument
* @param datePart - refers to DAY, MONTH Or YEAR
* @returns {number} - the difference between dateTimeArgument1 and dateTimeArgument2 expressed in the specified datePart
*/
DATEDIFF(dateTimeArgument1, dateTimeArgument2, datePart): number
Example
/*
Input:
dateTimeArgument1 = "2021-05-25T00:00:00.000Z";
dateTimeArgument2 = "2021-05-05T00:00:00.000Z";
datePart = DATEPART.DAY;
Output:
result = 20;
*/
result = DATEDIFF(dateTimeArgument1, dateTimeArgument2, datePart);
Returns the difference of months between two dates.
Syntax
/**
* @param dateTimeArgument1 {Date} - the first date argument
* @param dateTimeArgument2 {Date} - the second date argument
* @returns {number} - the difference of months between two dates
*/
DIFFMONTHS(dateTimeArgument1, dateTimeArgument2): number
Example
/*
/*
Input:
dateTimeArgument1 = "2021-08-01T00:00:00.000Z";
dateTimeArgument2 = "2021-07-11T00:00:00.000Z";
Output:
result = 1;
*/
result = DIFFMONTHS(dateTimeArgument1, dateTimeArgument2);
Returns the difference of months between two dates.
Syntax
/**
/**
* @param dateTimeArgument1 {Date} - the first date argument
* @param dateTimeArgument2 {Date} - the second date argument
* @returns {number} - the difference of months between two dates
*/
DIFFMONTHSEXACT(dateTimeArgument1, dateTimeArgument2): number
Example
/*
Input:
dateTimeArgument1 = "2021-08-01T00:00:00.000Z";
dateTimeArgument2 = "2021-07-11T00:00:00.000Z";
Output:
result = 0;
*/
result = DIFFMONTHSEXACT(dateTimeArgument1, dateTimeArgument2);
Returns the difference of years between two dates.
Syntax
/**
* @param dateTimeArgument1 {Date} - the first date argument
* @param dateTimeArgument2 {Date} - the second date argument
* @returns {number} - the difference of years between two dates
*/
DIFFYEARS(dateTimeArgument1, dateTimeArgument2): number
Example
/*
Input:
dateTimeArgument1 = "2022-08-01T00:00:00.000Z";
dateTimeArgument2 = "2021-11-11T00:00:00.000Z";
Output:
result = 1;
*/
result = DIFFYEARS(dateTimeArgument1, dateTimeArgument2);
Returns the difference of years between two dates.
Syntax
/**
* @param dateTimeArgument1 {Date} - the first date argument
* @param dateTimeArgument2 {Date} - the second date argument
* @returns {number} - the difference of years between two dates
*/
DIFFYEARSEXACT(dateTimeArgument1, dateTimeArgument2): number
Example
/*
Input:
dateTimeArgument1 = "2022-08-01T00:00:00.000Z";
dateTimeArgument2 = "2021-11-11T00:00:00.000Z";
Output:
result = 0;
*/
result = DIFFYEARSEXACT(dateTimeArgument1, dateTimeArgument2);
Return the first day of month.
Syntax
/**
* @param dateTimeArgument {Date} - the date argument
* @returns {Date} - the first day of month
*/
FIRSTDAYOFMONTH(dateTimeArgument): Date
Example
/*
Input:
dateTimeArgument = "2022-08-21T00:00:00.000Z";
Output:
result = "2022-08-01T00:00:00";
*/
result = FIRSTDAYOFMONTH(dateTimeArgument);
Return the last day of month.
Syntax
/**
* @param dateTimeArgument {Date} - the date argument
* @returns {Date} - the last day of month
*/
LASTDAYOFMONNTH(dateTimeArgument): Date
Example
/*
Input:
dateTimeArgument = "2022-08-21T00:00:00.000Z";
Output:
result = "2022-08-31T00:00:00";
*/
result = LASTDAYOFMONNTH(dateTimeArgument);
Data Set Calls
To extract value mappings from data sets (see Data Sets for details), use the following syntax:
DataSet("<data set name>", ("<discriminant 1 name>", <discriminant 1 value>), ("<discriminant 2 name>", <discriminant 2 value>) ... )
If all the discriminants are given as a parameter, the result will be a value. However, if one of the discriminants is not sent as a parameter, the result will be an array.
In the example below, we return a risk coefficient from a data set called RiskPrice with two discriminants (structure_type and risk_type). We retrieve the value corresponding to the structure and risk stored in the myStructure and myRisk input parameters.
The sub-types that are text are written using quotation marks.
result = DataSet("RiskPrice", ("structure_type", myStructure), ("risk_type", myRisk));