Excel-like Function

This documentation provides detailed information about various functions available for use, including descriptions, purposes, syntax, usage examples with static values, and usage examples with custom tokens. The functions covered include SUM, SUM_LIST, AVG, AVG_LIST, MIN, MIN_LIST, MAX, MAX_LIST, DAY, MONTH, COUNT, YEAR, MINUTE, HOUR, SECOND, WEEK, NOW, TODAY, DATEDIFF, DATECOMPUTE, POW, SQRT, PMT, PV, EMI, LIMIT, SORT, and FILTER.

How to Use the Functions?

You can utilize these functions within the formula editor and the Custom JS editor. Here’s a step-by-step guide to using them effectively:

1. Formula Editor

The formula editor allows you to input and evaluate expressions using the available functions. Here's how to proceed:

Open the Formula editor: You can open formula editor wherever it is available.
Input Your Formula: Use the functions as part of your expression. For example, to calculate the sum of a list, you can write:
```
SUM(1, 2, 3, 4)
```
Evaluate: Execute the formula to get the result.

2. Custom JS Editor

The Custom JS editor provides a more flexible environment for writing and executing JavaScript code, including the use of these functions.

Open the Custom JS Editor: Access the editor where you can write custom JavaScript.
Write Your Code: Incorporate the functions within your script. For instance, to compute the average of an array:
```
let numbers = [1, 2, 3, 4];
let average = AVG(...numbers);
console.log(average);
```
Run the Script: Execute your script to see the results.

By using these editors, you can effectively integrate and apply the functions to achieve your desired calculations and logic.

Available Functions List

The available functions are grouped into categories based on their functionality. These include arithmetic functions, date and time functions, and array manipulation functions.

1. SUM

The SUM function calculates the total sum of a series of numbers provided as arguments.

Syntax: The syntax of the SUM function is straightforward. Here is how you can write it:

SUM(number1, number2, ..., numberN)

In the SUM function, you can use static values but not any custom tokens. Here are some examples:

SUM(1, 2, 3, 4) > 5 // true

SUM(1, 2, 3, 4) === 10 // true

SUM(1, 2, 3, 4) // 10

SUM(5, 10, 15) // 30

2. SUM_LIST

The SUM_LIST function calculates the sum of the elements in a list. Here is the syntax of the SUM_LIST function:

SUM_LIST(list, key)

You can use this function with static values and with custom token values, like this:

With Static Values:

SUM_LIST(list=[1,2,3,4]) // 10

With Custom Tokens:

SUM_LIST(list={{.customInput.Loan_amount}}) > 10000 // true (if the sum of the loan amounts is greater than 10000)

SUM_LIST(list={{.customInput.Scores}}, 'value') === 50 // true (if the sum of the 'value' key in the objects of the list is 50)

SUM_LIST(list={{.customInput.Scores}}, 'value') // sum of the 'value' key in the objects of the list

3. AVG

The AVG function calculates the average of a series of numbers provided as arguments.

Syntax: The syntax of the AVG function is straightforward. Here is how you can write it:

AVG(number1, number2, ..., numberN)

In the AVG function, you can use static values but not any custom tokens. Here are some examples:

AVG(1, 2, 3, 4) > 2 // true

AVG(1, 2, 3, 4) === 2.5 // true

AVG(1, 2, 3, 4) // 2.5

AVG(5, 10, 15) // 10

4. AVG_LIST

The AVG_LIST function calculates the average of the elements in a list. Here is the syntax of the AVG_LIST function:

AVG_LIST(list, key)

You can use this function with static values and with custom token values, like this:

With Static Values:

AVG_LIST(list=[1,2,3,4]) // 2.5

With Custom Tokens:

AVG_LIST(list={{.customInput.Loan_amount}}) > 5000 // true (if the average of the loan amounts is greater than 5000)

AVG_LIST(list={{.customInput.Scores}}, 'value') === 25 // true (if the average of the 'value' key in the objects of the list is 25)

AVG_LIST(list={{.customInput.Scores}}, 'value') // average of the 'value' key in the objects of the list

5. MIN

The MIN function finds the smallest number in a given set of numbers.

Syntax: The syntax of the MIN function is straightforward. Here is how you can write it:

MIN(number1, number2, ..., numberN)

In the MIN function, you can use static values but not any custom tokens. Here are some examples:

MIN(1, 2, 3, 4) < 2 // true

MIN(1, 2, 3, 4) === 1 // true

MIN(1, 2, 3, 4) // 1

MIN(5, 10, 15) // 5

6. MIN_LIST

The MIN_LIST function finds the smallest number in a list. Here is the syntax of the MIN_LIST function:

MIN_LIST(list, key)

You can use this function with static values and with custom token values, like this:

With Static Values:

MIN_LIST(list=[1,2,3,4]) // 1

With Custom Tokens:

MIN_LIST(list={{.customInput.Loan_amount}}) < 1000 // true (if the minimum loan amount is less than 1000)

MIN_LIST(list={{.customInput.Scores}}, 'value') === 10 // true (if the minimum 'value' key in the objects of the list is 10)

MIN_LIST(list={{.customInput.Scores}}, 'value') // minimum of the 'value' key in the objects of the list

7. MAX

The MAX function finds the largest number in a given set of numbers.

Syntax: The syntax of the MAX function is straightforward. Here is how you can write it:

MAX(number1, number2, ..., numberN)

In the MAX function, you can use static values but not any custom tokens. Here are some examples:

MAX(1, 2, 3, 4) > 3 // true

MAX(1, 2, 3, 4) === 4 // true

8. MAX_LIST

The MAX_LIST function finds the largest number in a list. Here is the syntax of the MAX_LIST function:

MAX_LIST(list, key)

You can use this function with static values and with custom token values, like this:

With Static Values:

MAX_LIST(list=[1,2,3,4]) // 4

With Custom Tokens:

MAX_LIST(list={{.customInput.Loan_amount}}) > 10000 // true (if the maximum loan amount is greater than 10000)

MAX_LIST(list={{.customInput.Scores}}, 'value') === 50 // true (if the maximum 'value' key in the objects of the list is 50)

MAX_LIST(list={{.customInput.Scores}}, 'value') // maximum of the 'value' key in the objects of the list

9. DAY

The DAY function returns the day of the month from a given date.

Syntax: The syntax of the DAY function is straightforward. Here is how you can write it:

DAY(date)

In the DAY function, you can use static values but not any custom tokens. Here are some examples:

DAY('2024-06-28') // 28

DAY('2023-01-01') // 1

DAY('2022-12-31') // 31

10. MONTH

The MONTH function returns the month from a given date.

Syntax: The syntax of the MONTH function is straightforward. Here is how you can write it:

MONTH(date)

In the MONTH function, you can use static values but not any custom tokens. Here are some examples:

MONTH('2024-06-28') // 6

MONTH('2023-01-01') // 1

MONTH('2022-12-31') // 12

11. YEAR

The YEAR function returns the year from a given date.

Syntax: The syntax of the YEAR function is straightforward. Here is how you can write it:

YEAR(date)

In the YEAR function, you can use static values but not any custom tokens. Here are some examples:

YEAR('2024-06-28') // 2024

YEAR('2023-01-01') // 2023

YEAR('2022-12-31') // 2022

12. MINUTE

The MINUTE function returns the minute component of a given datetime.

Syntax: The syntax of the MINUTE function is straightforward. Here is how you can write it:

MINUTE(datetime)

In the MINUTE function, you can use static values but not any custom tokens. Here are some examples:

MINUTE('2024-06-28 14:30:45') // 30

MINUTE('2023-01-01 00:59:01') // 59

MINUTE('2022-12-31 23:00:59') // 0

13. HOUR

The HOUR function returns the hour component of a given datetime.

Syntax: The syntax of the HOUR function is straightforward. Here is how you can write it:

HOUR(datetime)

In the HOUR function, you can use static values but not any custom tokens. Here are some examples:

HOUR('2024-06-28 14:30:45') // 14

HOUR('2023-01-01 23:59:01') // 23

HOUR('

2022-12-31 00:00:59') // 0

14. SECOND

The SECOND function returns the second component of a given datetime.

Syntax: The syntax of the SECOND function is straightforward. Here is how you can write it:

SECOND(datetime)

In the SECOND function, you can use static values but not any custom tokens. Here are some examples:

SECOND('2024-06-28 14:30:45') // 45

SECOND('2023-01-01 00:00:01') // 1

SECOND('2022-12-31 23:59:59') // 59

15. WEEK

The WEEK function returns the week number of a given date.

Syntax: The syntax of the WEEK function is straightforward. Here is how you can write it:

WEEK(date)

In the WEEK function, you can use static values but not any custom tokens. Here are some examples:

WEEK('2024-06-28') // 26

WEEK('2023-01-01') // 1

WEEK('2022-12-31') // 52

16. NOW

The NOW function returns the current date and time in the current locale.

Syntax: The syntax of the NOW function is straightforward. Here is how you can write it:

NOW()

In the NOW function, you can use static values but not any custom tokens. Here are some examples:

NOW() // current date and time

NOW('America/New_York') // current date and time in specified timezone

17. TODAY

The TODAY function returns the current date in the current locale.

Syntax: The syntax of the TODAY function is straightforward. Here is how you can write it:

TODAY()

In the TODAY function, you can use static values but not any custom tokens. Here are some examples:

TODAY() // current date

TODAY('America/New_York') // current date in specified timezone

18. DATEDIFF

The DATEDIFF function calculates the difference between two specified dates or timestamps.

Syntax: The syntax of the DATEDIFF function is straightforward. Here is how you can write it:

DATEDIFF(date1, date2, unit)
DATEDIFF(timestamp1, timestamp2, unit)

You can use this function with static values. Here are some examples:

DATEDIFF('2024-06-25', '2024-06-20', 'days') // 5

DATEDIFF('2024-06-25', '2024-01-01', 'months') // 5

DATEDIFF('2024-06-25', '2023-01-01', 'years') // 1

DATEDIFF('2024-06-25 12:00:00', '2024-06-25 10:00:00', 'hours') // 2

DATEDIFF('2024-06-25 12:30:00', '2024-06-25 12:00:00', 'minutes') // 30

DATEDIFF('2024-06-25 12:00:30', '2024-06-25 12:00:00', 'seconds') // 30

19. DATECOMPUTE

The DATECOMPUTE function computes a new date by adding or subtracting a specified amount of time from a given date.

Syntax: The syntax of the DATECOMPUTE function is straightforward. Here is how you can write it:

DATECOMPUTE(date, value, unit)

You can use this function with static values. Here are some examples:

DATECOMPUTE('2024-06-25', '+2', 'days') // '2024-06-27'

DATECOMPUTE('2024-06-25', '-5', 'days') // '2024-06-20'

DATECOMPUTE('2024-06-25', '+3', 'months') // '2024-09-25'

DATECOMPUTE('2024-06-25', '-2', 'months') // '2024-04-25'

DATECOMPUTE('2024-06-25', '+1', 'years') // '2025-06-25'

DATECOMPUTE('2024-06-25', '-5', 'years') // '2019-06-25'

DATECOMPUTE('2024-06-25T12:00:00', '+6', 'hours') // '2024-06-25T18:00:00'

DATECOMPUTE('2024-06-25T12:00:00', '-3', 'hours') // '2024-06-25T09:00:00'

20. POW

The POW function raises a number to the power of another number.

Syntax: The syntax of the POW function is straightforward. Here is how you can write it:

POW(base, exponent)

In the POW function, you can use static values but not any custom tokens. Here are some examples:

POW(5, 2) // 25

POW(10, 0) // 1

21. SQRT

The SQRT function returns the square root of a number.

Syntax: The syntax of the SQRT function is straightforward. Here is how you can write it:

SQRT(number)

In the SQRT function, you can use static values but not any custom tokens. Here are some examples:

SQRT(25) // 5

SQRT(9) // 3

22. PMT

The PMT function calculates the payment for a loan based on constant payments and a constant interest rate.

Syntax: The syntax of the PMT function is straightforward. Here is how you can write it:

PMT(rate, nper, pv)

You can use this function with static values. Here are some examples:

PMT(0.04/12, 36, 5000) // -147.22

PMT(0.03/12, 12, 2000) // -170.92

PMT(0.05/12, 60, 10000) // -188.71

23. PV

The PV function calculates the present value of an investment based on a series of future payments.

Syntax: The syntax of the PV function is straightforward. Here is how you can write it:

PV(rate, nper, pmt)

You can use this function with static values. Here are some examples:

PV(0.05/12, 60, -188.71) // 10000

PV(0.04/12, 36, -147.22) // 5000

PV(0.03/12, 12, -170.92) // 2000

24. EMI

The EMI function calculates the Equated Monthly Installment (EMI) for a loan.

Syntax: The syntax of the EMI function is straightforward. Here is how you can write it:

EMI(pv, rate, nper, [fv], [type])

You can use this function with static values. Here are some examples:

EMI(5000, 0.04/12, 36) // 147.22

EMI(2000, 0.03/12, 12) // 170.92

EMI(10000, 0.05/12, 60, 0, 0) // 188.71

EMI(10000, 0.05/12, 60, 0, 500) // 188.71

25. LIMIT

The LIMIT function limits the number of elements returned in an array to a specified number.

Syntax: The syntax of the LIMIT function is straightforward. Here is how you can write it:

LIMIT(array, limit)

You can use this function with static values. Here are some examples:

LIMIT([1, 2, 3, 4, 5], 3) // [1, 2, 3]

LIMIT([{ id: 1, name: 'John' }, { id: 2, name: 'Jane' }, { id: 3, name: 'Doe' }], 2) // [{ id: 1, name: 'John' }, { id: 2, name: 'Jane' }]

26. SORT

The SORT function sorts a list of numbers or objects based on specified criteria.

Syntax: The syntax of the SORT function is straightforward. Here is how you can write it:

SORT(array, order, [key1, key2, ...])

You can use this function with static values. Here are some examples:

With Numbers:

SORT

([5, 3, 8, 1, 2], 'ASC') // [1, 2, 3, 5, 8]

SORT([5, 3, 8, 1, 2], 'DESC') // [8, 5, 3, 2, 1]

With Objects:

SORT([{ id: 3, name: 'John' }, { id: 1, name: 'Jane' }, { id: 2, name: 'Doe' }], 'ASC', 'id') // [{ id: 1, name: 'Jane' }, { id: 2, name: 'Doe' }, { id: 3, name: 'John' }]

SORT([{ id: 3, name: 'John' }, { id: 1, name: 'Jane' }, { id: 2, name: 'Doe' }], 'DESC', 'id') // [{ id: 3, name: 'John' }, { id: 2, name: 'Doe' }, { id: 1, name: 'Jane' }]

SORT([{ id: 3, name: 'John', age: 30 }, { id: 1, name: 'Jane', age: 25 }, { id: 2, name: 'Doe', age: 28 }], 'ASC', 'name', 'age') // [{ id: 1, name: 'Jane', age: 25 }, { id: 2, name: 'Doe', age: 28 }, { id: 3, name: 'John', age: 30 }]

SORT([{ id: 3, name: 'John', age: 30 }, { id: 1, name: 'Jane', age: 25 }, { id: 2, name: 'Doe', age: 28 }], 'DESC', 'name', 'age') // [{ id: 3, name: 'John', age: 30 }, { id: 2, name: 'Doe', age: 28 }, { id: 1, name: 'Jane', age: 25 }]

27. FILTER

The FILTER function filters elements in an array based on specified criteria.

Syntax: The syntax of the FILTER function is straightforward. Here is how you can write it:

FILTER(array, condition, [key1, key2, ...])

You can use this function with static values. Here are some examples:

FILTER([{k1: 1, k2: "s"}, {k1: 2, k2: "b"}, {k1: 4, k2: "apple"}], "$item.$k1 <= (1 + 3) && $item.$k2.length > 0 && $item.$k2[0] != 'p' && $item.$k1 > 1") // [{k1: 2, k2: "b"}]

FILTER([{k1: 1, k2: "s"}, {k1: 2, k2: "b"}, {k1: 4, k2: "d"}], "$item.$k1 > 1", "k1") // [{k1: 2}, {k1: 4}]

FILTER([{k1: 1, k2: "s"}, {k1: 2, k2: "b"}, {k1: 4, k2: "d"}], "$item.$k1 < 3", "k1", "k2") // [{k1: 1, k2: "s"}, {k1: 2, k2: "b"}]

FILTER([{k1: 1, k2: "s"}, {k1: 2, k2: "bb"}, {k1: 4, k2: "d"}], "$item.$k2.length === 1") // [{k1: 1, k2: "s"}, {k1: 4, k2: "d"}]

28. COUNT

The COUNT function returns the total number of items in an array or list, providing a quick way to determine the size of the collection.

Syntax: The syntax of the COUNT function is straightforward. Here is how you can write it:

COUNT(array)

Using:

With Static Values:

COUNT([1, 2, 3, 4]) // 4

COUNT(['apple', 'banana', 'cherry']) // 3

With Custom Tokens:

COUNT({{.customInput.listObjs}}) // Returns the count of elements in the custom input list

Output: A numerical value representing the total number of elements in the array or list.

29. DATEPARSE

The DATEPARSE function transforms a date or datetime string into a date object in Nected's supported RFC 3339 format.

Syntax:

DATEPARSE(date : string , format : string)

date: The date or datetime string you want to transform.
format: The format of the input date string.

Returns: A date/datetime object in RFC3339 format.

Examples:

Example 1:

DATEPARSE({{.customInput.dateTimeToken}}, 'mm-dd-yyyy')
Output: 2024-06-12T00:00:00Z

Example 2:

DATEPARSE('2007-06-09 17:46:21', 'yyyy-mm-dd HH:MM:ss')
Output: 2010-10-09T17:00:21Z

Example 3:

DATEPARSE('06-12-2024', 'mm-dd-yyyy')
Output: 2024-06-12T00:00:00Z

30. DATEFORMAT

The DATEFORMAT function formats a date or datetime string/object into a specified format.

Syntax:

DATEFORMAT(date: string|dateObj , format : string)

date: The date or datetime string/object you want to format.
format: The desired format of the output.

Returns: A string representing the formatted date/time.

Examples:

Example 1:

DATEFORMAT({{.customInput.dateTimeToken}}, 'ddd mmm dd yyyy HH:MM:ss')
Output: Sat Jun 09 2007 17:46:21

Example 2:

DATEFORMAT('2007-06-09 17:46:21', 'ddd mmm dd yyyy HH:MM:ss')
Output: Sat Jun 09 2007 17:46:21

Example 3:

DATEFORMAT('2007-06-09', 'm/d/yy')
Output: 6/9/07

Example 4:

DATEFORMAT('2007-06-09', 'mm/dd/yyyy')
Output: 06/09/2007

Example 5:

DATEFORMAT('2007-06-09', 'mmm d, yyyy')
Output: Jun 9, 2007

Example 6:

DATEFORMAT('2007-06-09', 'mmmm d, yyyy')
Output: June 9, 2007

Example 7:

DATEFORMAT('2007-06-09', 'dddd, mmmm d, yyyy')
Output: Saturday, June 9, 2007

Example 8:

DATEFORMAT('2007-06-09 17:46:21', 'h:MM TT')
Output: 5:46 PM

Example 9:

DATEFORMAT('2007-06-09 17:46:21', 'H:MM:ss TT')
Output: 5:46:21 PM

Example 10:

DATEFORMAT('2007-06-09', 'yyyy-mm-dd')
Output: 2007-06-09

Example 11:

DATEFORMAT('2007-06-09 17:46:21', 'HH:MM:ss')
Output: 17:46:21

Conclusion

These notes give a tutorial-like explanation of all the facilities provided by our system with essential reference to arithmetic functions and their constants, date and time functions, and array-manipulating functions. For each defined function, the function description is given along with its purpose, syntax, examples of usage, and anticipated result.

The arithmetic functions such as SUM, SUM_LIST, AVG, AVG_LIST, MIN, MIN_LIST, MAX, and MAX_LIST enable users to do basic mathematical calculations of numbers or lists of numbers. These functions can accept not only static values but also the computed values by the application and other tokens making the functions useful in various points of applications.

The types of functions include the date and time functions which include; DAY, MONTH, YEAR, MINUTE, HOUR, second, week, now, today, DATEDIFF, and DATECOMPUTE. These functions provide for such basic functions as extraction of components of dates, calculations of differences between dates, and computations of new dates taking into consideration certain parameters.

The array manipulation functions including LIMIT, SORT, and FILTER enable proficient handling and processing of the arrays. These functions allow a programmer to sort, filter, and maybe limit the number of elements to be contained in an array; and as such, data manipulation of these arrays is simple and fast.

If one comprehends and applies these functions, then the users can improve their data processing to achieve efficiency and constructiveness in performances giving a view to the exactness of significance. This documentation is thus useful in the best exploitation of features in the various functions of our system.

PreviousList Editor NextWhite-Labelling

Last updated 1 month ago