Skip to content

Range functions

RangeAvg

RangeAvg() returns the average of a range. Input to the function can be either a range of values or an expression.

RangeAvg(first_expr[, Expression])

Return data type: numeric

The arguments of this function may contain inter-record functions which in themselves return a list of values.

Argument Description
first_expr The expression or field containing the data to be measured.
Expression Optional expressions or fields containing the range of data to be measured.

Limitations: If no numeric value is found, NULL is returned.

Examples Results
RangeAvg(1,2,4) Returns 2.33333333
RangeAvg(1,'xyz) Returns 1
RangeAvg(null(), 'abc') Returns NULL

Add the example script to your app and run it.

RangeTab3:
LOAD recno() as RangeID, RangeAvg(Field1,Field2,Field3) as MyRangeAvg INLINE [
Field1, Field2, Field3
10,5,6
2,3,7
8,2,8
18,11,9
5,5,9
9,4,2
];
The resulting table shows the returned values of MyRangeAvg for each of the records in the table.
RangeID MyRangeAvg
1 7
2 4
3 6
4 12.666
5 6.333
6 5

Example with expression:

RangeAvg(Above(MyField),0,3))

Returns a sliding average of the result of the range of three values of MyField calculated on the current row and two rows above the current row. By specifying the third argument as 3, the Above() function returns three values, where there are sufficient rows above,which are taken as input to the RangeAvg() function.

Data used in examples: | MyField | RangeAvg(Above(MyField,0,3)) | Explanation | | ---------- | --------------------------------------------------------------------------- | - | | 10 | 10 | Because this is the top row, the range consists of one value only. | | 2 | 6 | There is only one row above this row, so the range is: 10,2. | | 8 | 6.6666666667 | The equivalent to RangeAvg(10,2,8) | | 18 | 9.333333333 | | | 5 | 10.333333333 | | | 9 | 10.6666666667 | |

RangeTab:
LOAD * INLINE [
MyField
10
2
8
18
5
9
] ;

RangeCorrel

RangeCorrel() returns the correlation coefficient for two sets of data. The correlation coefficient is a measure of the relationship between the data sets.

RangeCorrel(x_value, y_value[, Expression])

Return data type: numeric

Data series should be entered as (x,y) pairs. For example, to evaluate two series of data, array 1 and array 2, where the array 1 = 2,6,9 and array 2 = 3,8,4 you would write RangeCorrel (2,3,6,8,9,4) which returns 0.269.

Argument Description
x-value, y-value Each value represents a single value or a range of values as returned by an inter-record functions with a third optional parameter. Each value or range of values must correspond to an x-value or a range of y-values.
Expression Optional expressions or fields containing the range of data to be measured.

Limitations:

The function needs at least two pairs of coordinates to be calculated.
Text values, NULL values and missing values return NULL.

Examples Results
RangeCorrel (2,3,6,8,9,4,8,5) Returns 0.2492. This function can be loaded in the script or added into a visualization in the expression editor.

Add the example script to your app and run it.

Load * Inline [
ID1|x1|y1|x2|y2|x3|y3|x4|y4|x5|y5|x6|y6
01|46|60|70|13|78|20|45|65|78|12|78|22
02|65|56|22|79|12|56|45|24|32|78|55|15
03|77|68|34|91|24|68|57|36|44|90|67|27
04|57|36|44|90|67|27|57|68|47|90|80|94
] (delimiter is '|'); XY:
LOAD recno() as RangeID, \* Inline [
X|Y
2|3
6|8
9|4
8|5
](delimiter is '|');
In a table with ID1 as a dimension and the measure: RangeCorrel(x1,y1,x2,y2,x3,y3,x4,y4,x5,y5,x6,y6)), the RangeCorrel() function finds the value of Correl over the range of six x,y pairs, for each of the ID1 values.
ID1 MyRangeCorrel
01 -0.9517
02 -0.5209
03 -0.5209
04 -0.1599

XY:
LOAD recno() as RangeID, * Inline [
X|Y
2|3
6|8
9|4
8|5
](delimiter is '|');

In a table with RangeID as a dimension and the measure: RangeCorrel(Below(X,0,4,BelowY,0,4)), the RangeCorrel() function uses the results of the Below() functions, which because of the third argument (count) set to 4, produce a range of four x-y values from the loaded table XY.
RangeID MyRangeCorrel2
01 0.2492
02 -0.9959
03 -1.0000
04 -
The value for RangeID 01 is the same as manually entering RangeCorrel(2,3,6,8,9,4,8,5). For the other values of RangeID, the series produced by the Below() function are: (6,8,9,4,8,5), (9,4,8,5), and (8,5), the last of which produces a null result.

RangeCount

RangeCount() returns the number of values, both text and numeric, in the expression or field.

RangeCount(first_expr[, Expression]) Return data type: integer

The arguments of this function may contain inter-record functions which in themselves return a list of values.

Argument Description
first_expr The expression or field containing the data to be counted.
Expression Optional expressions or fields containing the range of data to be counted.

Limitations: NULL values are not counted.

Examples Results
RangeCount (1,2,4) Returns 3
RangeCount(2,'xyz') Returns 2
RangeCount(null( )) Returns 0
RangeCount(2,'xyz', null()) Returns 2

Add the example script to your app and run it.

RangeTab3:
LOAD recno() as RangeID, RangeCount(Field1,Field2,Field3) as MyRangeCount INLINE [
Field1, Field2, Field3
10,5,6
2,3,7
8,2,8
18,11,9
5,5,9
9,4,2
];
The resulting table shows the returned values of MyRangeCount for each of the records in the table.
RangeID MyRangeCount
1 3
2 3
3 3
4 3
5 3
6 3

Example with expression:

RangeCount(Above(MyField,1,3))

Returns the number of values contained in the three results of MyField. By specifying the second and third arguments of the Above() function as 3, it returns the values from the three fields above the current row, where there are sufficient rows, which are taken as input to the RangeSum() function.

Data used in examples: | MyField | RangeCount(Above(MyField,1,3)) | | ------- | ------------------------------ | | 10 | 0 | | 2 | 1 | | 8 | 2 | | 18 | 3 | | 5 | 3 | | 9 | 3 |

RangeTab:
LOAD * INLINE [
MyField
10
2
8
18
5
9
] ;

RangeFractile

RangeFractile() returns the value that corresponds to the n-th fractile (quantile) of a range of numbers.

Tip

RangeFractile() uses linear interpolation between closest ranks when calculating the fractile.

RangeFractile(fractile, first_expr[, Expression])

Return data type: numeric

The arguments of this function may contain inter-record functions which in themselves return a list of values.

Argument Description
fractile A number between 0 and 1 corresponding to the fractile (quantile expressed as a fraction) to be calculated.
first_expr The expression or field containing the data to be measured.
Expression Optional expressions or fields containing the range of data to be counted.

Add the example script to your app and run it.

RangeTab:
LOAD recno() as RangeID,
RangeFractile(0.5,Field1,Field2,Field3)
as MyRangeFrac INLINE [
Field1, Field2, Field3
10,5,6
2,3,7
8,2,8
18,11,9
5,5,9
9,4,2
];
The resulting table shows the returned values of MyRangeFrac for each of the records in the table.
RangeIDMyRangeFrac
16
23
38
411
55
64

Examples:

Examples Results
RangeFractile(0.24,1,2,4,6) Returns 1.72
RangeFractile(0.5,1,2,3,4,6) Returns 3
RangeFractile (0.5,1,2,5,6) Returns 3.5

Example with expression:

RangeFractile(0.5, Above(Sum(MyField),0,3))

In this example, the inter-record function Above() contains the optional offset and count arguments. This produces a range of results that can be used as input to the any of the range functions. In this case, Above(Sum(MyField),0,3) returns the values of MyField for the current row and the two rows above. These values provide the input to the RangeFractile() function. So, for the bottom row in the table below, this is the equivalent of RangeFractile(0.5, 3,4,6), that is, calculating the 0.5 fractile for the series 3, 4, and 6. The first two rows in the table below, the number of values in the range is reduced accordingly, where there no rows above the current row. Similar results are produced for other inter-record functions.

MyField RangeFractile(0.5, Above(Sum(MyField),0,3))
1 1
2 1.5
3 2
4 3
5 4
6 5

Data used in examples:

RangeTab:
LOAD * INLINE [
MyField
10
2
8
18
5
9
] ;

RangeIRR

RangeIRR() returns the internal rate of return for a series of cash flows represented by the input values.

The internal rate of return is the interest rate received for an investment consisting of payments (negative values) and income (positive values) that occur at regular periods.

RangeIRR(value[, value][, Expression])

Return data type: numeric

Argument Description
value A single value or a range of values as returned by an inter record function with a third optional parameter. The function needs at least one positive and one negative value to be calculated.
Expression Optional expressions or fields containing the range of data to be measured.

Limitations: Text values, NULL values and missing values are disregarded.

Examples Results
RangeIRR(-70000,12000,15000,18000,21000,26000) Returns 0.0866
RangeTab3:
LOAD *, recno() as RangeID,
RangeIRR(Field1,Field2,Field3) as RangeIRR;
LOAD * INLINE [
Field1|Field2|Field3
-10000|5000|6000
-2000|NULL|7000
-8000|'abc'|8000
-1800|11000|9000
-5000|5000|9000
-9000|4000|2000
] (delimiter is '|');
The resulting table shows the returned values of RangeIRR for each of the records in the table.
RangeID RangeIRR
1 0.0639
2 0.8708
3 -
4 5.8419
5 0.9318
6 -0.2566

RangeKurtosis

RangeKurtosis() returns the value that corresponds to the kurtosis of a range of numbers.

RangeKurtosis(first_expr[, Expression])

Return data type: numeric

The arguments of this function may contain inter-record functions which in themselves return a list of values. | Argument | Description | | ---------- | ----------- | | first_expr | The expression or field containing the data to be measured. | | Expression | Optional expressions or fields containing the range of data to be measured.|

Limitations: If no numeric value is found, NULL is returned.

Examples Results
RangeKurtosis (1,2,4,7) Returns -0.28571428571429

RangeMax

RangeMax() returns the highest numeric values found within the expression or field.

RangeMax(first_expr[, Expression])

Return data type: numeric

Argument Description
first_expr The expression or field containing the data to be measured.
Expression Optional expressions or fields containing the range of data to be measured.

Limitations: If no numeric value is found, NULL is returned.

Examples Results
RangeMax(1,2,4) Returns 4
RangeMax(1,'xyz') Returns 1
RangeMax(null(), 'abc') Returns NULL
>RangeTab3:
LOAD recno() as RangeID,
RangeMax(Field1,Field2,Field3) as MyRangeMax;
INLINE [
Field1,Field2,Field3
10,5,6
2,3,7
8,2,8
18,11,9
5,5,9
9,4,2
];
The resulting table shows the returned values of MyRangeMax for each of the records in the table.
RangeID MyRangeMax
1 10
2 7
3 8
4 18
5 9
6 9

Example with expression:

RangeMax(Above(MyField,0,3))

Returns the maximum value in the range of three values of MyField calculated on the current row and two rows above the current row. By specifying the third argument as 3, the Above() function returns three values, where there are sufficient rows above, which are taken as input to the RangeMax() function.

Data used in examples:

MyField RangeMax(Above(Sum(MyField),1,3))
10 10
2 10
8 10
18 18
5 18
9 18
RangeTab:
LOAD * INLINE [
MyField
10
2
8
18
5
9
] ;

RangeMaxString

RangeMaxString() returns the last value in the text sort order that it finds in the expression or field.

RangeMaxString(first_expr[, Expression])

Return data type: string

The arguments of this function may contain inter-record functions which in themselves return a list of values.

Argument Description
first_expr The expression or field containing the data to be measured.
Expression Optional expressions or fields containing the range of data to be measured.
Examples Results
RangeMaxString(1,2,4) Returns 4
RangeMaxString('xyz','abc') Returns 'xyz'
RangeMaxString(5,'abc') Returns 'abc'
RangeMaxString(null()) Returns NULL

Example with expression:

RangeMaxString(Above(MaxString(MyField),0,3))

Returns the last (in text sort order) of the three results of the MaxString(MyField) function evaluated on the current row and two rows above the current row.

Data used in examples: | MyField | RangeMaxString(Above(MaxString(MyField),0,3)) | | ------- | - | | 10 | 10 | | abc | abc | | 8 | abc | | def | def | | xyz | xyz | | 9 | xyz |

RangeTab:
LOAD * INLINE [
MyField
10
'abc'
8
'def'
'xyz'
9
] ;

RangeMin

RangeMin() returns the lowest numeric values found within the expression or field.

RangeMin(first_expr[, Expression])

Return data type: numeric

Argument Description
first_expr The expression or field containing the data to be measured.
Expression Optional expressions or fields containing the range of data to be measured.

Limitations: If no numeric value is found, NULL is returned.

Examples Results
RangeMin(1,2,4) Returns 1
RangeMin(1,'xyz') Returns 1
RangeMax(null(), 'abc') Returns NULL
RangeTab3:
LOAD recno() as RangeID,
RangeMin(Field1,Field2,Field3) as MyRangeMin;
INLINE [
Field1,Field2,Field3
10,5,6
2,3,7
8,2,8
18,11,9
5,5,9
9,4,2
];
The resulting table shows the returned values of MyRangeMin for each of the records in the table.
RangeID MyRangeMin
1 5
2 2
3 2
4 9
5 5
6 2

Example with expression:

RangeMin (Above(MyField,0,3)

Returns the minimum value in the range of three values of MyField calculated on the current row and two rows above the current row. By specifying the third argument as 3, the Above() function returns three values, where there are sufficient rows above, which are taken as input to the RangeMin() function.

Data used in examples: | MyField | RangeMin(Above(MyField,0,3)) | | ------- | - | | 10 | 10 | | 2 | 2 | | 8 | 2 | | 18 | 2 | | 5 | 5 | | 9 | 5 |

RangeTab:
LOAD * INLINE [
MyField
10
2
8
18
5
9
] ;

RangeMinString

RangeMinString() returns the first value in the text sort order that it finds in the expression or field.

RangeMinString(first_expr[, Expression])

Return data type: string

The arguments of this function may contain inter-record functions which in themselves return a list of values.

Argument Description
first_expr The expression or field containing the data to be measured.
Expression Optional expressions or fields containing the range of data to be measured.
Examples Results
RangeMinString(1,2,4) Returns 1
RangeMinString('xyz','abc') Returns 'abc'
RangeMinString(5,'abc') Returns 5
RangeMinString(null()) Returns NULL

Example with expression:

RangeMinString(Above(MinString(MyField),0,3))

Returns the first (in text sort order) of the three results of the MinString(MyField) function evaluated on the current row and two rows above the current row.

Data used in examples: | MyField | RangeMinString(Above(MinString(MyField),0,3)) | | ------- | - | | 10 | 10 | | abc | 10 | | 8 | 8 | | def | 8 | | xyz | 8 | | 9 | 9 |

RangeTab:
LOAD * INLINE [
MyField
10
'abc'
8
'def'
'xyz'
9
] ;

RangeMissingCount

RangeMissingCount() returns the number of non-numeric values (including NULL) in the expression or field.

RangeMissingCount(first_expr[, Expression])

Return data type: string

The arguments of this function may contain inter-record functions which in themselves return a list of values.

Argument Description
first_expr The expression or field containing the data to be counted.
Expression Optional expressions or fields containing the range of data to be counted.
Examples Results
RangeMissingCount(1,2,4) Returns 0
RangeMissingCount(5,'abc') Returns 1
RangeMissingCount(null()) Returns 1

Example with expression:

RangeMissingCount(Above(MinString(MyField),0,3))

Returns the number of non-numeric values in the three results of the MinString(MyField) function evaluated on the current row and two rows above the current row.

MyField RangeMissingCount(Above(MinString(MyField),0,3)) Explanation
10 2 Returns 2 because there are no rows above this row so 2 of the 3 values are missing.
abc 2 Returns 2 because there is only 1 row above the current row and the current row is non-numeric ('abc').
8 1 Returns 1 because 1 of the 3 rows includes a non-numeric ('abc').
def 2 Returns 2 because 2 of the 3 rows include non-numeric values ('def' and 'abc').
xyz 2 Returns 2 because 2 of the 3 rows include non-numeric values (' xyz' and 'def').
9 2 Returns 2 because 2 of the 3 rows include non-numeric values (' xyz' and 'def').

Data used in examples:

RangeTab:
LOAD * INLINE [
MyField
10
'abc'
8
'def'
'xyz'
9
] ;

RangeMode

RangeMode() finds the most commonly occurring value (mode value) in the expression or field.

RangeMax(first_expr[, Expression])

Return data type: numeric

The arguments of this function may contain inter-record functions which in themselves return a list of values.

Argument Description
first_expr The expression or field containing the data to be measured.
Expression Optional expressions or fields containing the range of data to be measured.

Limitations: If more than one value shares the highest frequency, NULL is returned.

Examples Results
RangeMode(1,2,9,2,4) Returns 2
RangeMode('a',4,'a',4) Returns NULL
RangeMode(null()) Returns NULL
RangeTab3:
LOAD recno() as RangeID,
RangeMode(Field1,Field2,Field3) as MyRangeMode;
INLINE [
Field1,Field2,Field3
10,5,6
2,3,7
8,2,8
18,11,9
5,5,9
9,4,2
];
The resulting table shows the returned values of MyRangeMode for each of the records in the table.
RangeID MyRangeMode
1 -
2 -
3 8
4 -
5 5
6 -

Example with expression:

RangeMode(Above(MyField,0,3))

Returns the most commonly occurring value in the three results of MyField evaluated on the current row and two rows above the current row. By specifying the third argument as 3, the Above() function returns three values, where there are sufficient rows above, which are taken as input to the RangeMode() function.

Data used in examples:

MyField RangeMode(Above(MyField,0,3))
10 Returns 10 because there are no rows above so the single value is the most commonly occurring.
2 -
8 -
18 -
5 -
9 -
RangeTab:
LOAD * INLINE [
MyField
10
'abc'
8
'def'
'xyz'
9
] ;

RangeNPV

RangeNPV() returns the net present value of an investment based on a discount rate and a series of future periodic payments (negative values) and incomes (positive values). The result has a default number format of money .

For cash flows that are not necessarily periodic, see RangeXNPV function.

RangeNPV(discount_rate, value[,value][, Expression])

Return data type: numeric

Argument Description
discount_rate The interest rate per period.
value A payment or income occurring at the end of each period. Each value may be a single value or a range of values as returned by an inter-record function with a third optional parameter.
Expression Optional expressions or fields containing the range of data to be measured.

Limitations: Text values, NULL values and missing values are disregarded.

Examples Results
RangeNPV(0.1,-10000,3000,4200,6800) Returns 1188.44
RangeTab3:
LOAD *,
recno() as RangeID,
RangeNPV(Field1,Field2,Field3) as RangeNPV;
LOAD * INLINE [
Field1|Field2|Field3
10|5|-6000
2|NULL|7000
8|'abc'|8000
18|11|9000
5|5|9000
9|4|2000
] (delimiter is '|');
The resulting table shows the returned values of RangeNPV for each of the records in the table.
RangeID RangeNPV
1 $-49.13
2 $777.78
3 $98.77
4 $25.51
5 $250.83
6 $20.40

RangeNullCount

RangeNullCount() finds the number of NULL values in the expression or field.

RangeNullCount(first_expr [, Expression])

Return data type: integer

The arguments of this function may contain inter-record functions which in themselves return a list of values.

Argument Description
first_expr The expression or field containing the data to be counted.
Expression Optional expressions or fields containing the range of data to be counted.

Examples:

Examples Results
RangeNullCount(1,2,4) Returns 0
RangeNullCount(5,'abc') Returns 0
RangeNullCount(null(), null()) Returns 2

Example with expression:

RangeNullCount (Above(Sum(MyField),0,3))

Returns the number of NULL values in the three results of the Sum(MyField) function evaluated on the current row and two rows above the current row.

MyField RangeNullCount(Above(Sum(MyField),0,3))
10 Returns 2 because there are no rows above this row so 2 of the 3 values are missing (=NULL).
'abc' Returns 1 because there is only one row above the current row, so one of the three values is missing (=NULL).
8 Returns 0 because none of the three rows is a NULL value.

Data used in examples:

RangeTab:
LOAD * INLINE [
MyField
10
'abc'
8
];

RangeNumericCount

RangeNumericCount() finds the number of numeric values in an expression or field.

RangeNumericCount(first_expr[, Expression])

Return data type: integer

The arguments of this function may contain inter-record functions which in themselves return a list of values.

Argument Description
first_expr The expression or field containing the data to be measured.
Expression Optional expressions or fields containing the range of data to be measured.

Examples:

Examples Results
RangeNumericCount(1,2,4) Returns 3
RangeNumericCount(5,'abc') Returns 1
RangeNumericCount(null()) Returns 0

Example with expression:

RangeNumericCount(Above(MaxString(MyField),0,3))

Returns the number of numeric values in the three results of the MaxString(MyField) function evaluated on the current row and two rows above the current row.

MyField RangeNumericCount(Above(MaxString(MyField),0,3))
10 1
abc 1
8 2
def 1
xyz 1
9 1

Data used in examples:

RangeTab:
LOAD * INLINE [
MyField
10
'abc'
8
'def'
'xyz'
9
];

RangeOnly

RangeOnly() is a dual function that returns a value if the expression evaluates to one unique value. If this is not the case then NULL is returned.

RangeOnly(first_expr[, Expression])

Return data type: dual

The arguments of this function may contain inter-record functions which in themselves return a list of values.

Argument Description
first_expr The expression or field containing the data to be measured.
Expression Optional expressions or fields containing the range of data to be measured.

Examples:

Examples Results
RangeOnly(1,2,4) Returns NULL
RangeOnly(5,'abc') Returns NULL
RangeOnly(null(), 'abc') Returns 'abc'
RangeOnly(10,10,10) Returns 10

RangeSkew

RangeSkew() returns the value corresponding to the skewness of a range of numbers.

RangeSkew(first_expr[, Expression])

Return data type: numeric

The arguments of this function may contain inter-record functions which in themselves return a list of values.

Argument Description
first_expr The expression or field containing the data to be measured.
Expression Optional expressions or fields containing the range of data to be measured.

Limitations: If no numeric value is found, NULL is returned.

Examples Results
RangeSkew(1,2,4) Returns 0.93521952958283
RangeSkew(above(SalesValue,0,3)) Returns a sliding skewness of the range of three values returned from the above() function calculated on the current row and the two rows above the current row.

Data used in example:

CustID RangeSkew(Above(SalesValue,0,3))
1-20 -, -, 0.5676, 0.8455, 1.0127, -0.8741, 1.7243, -1.7186, 1.5518, 1.4332, 0, 1.1066, 1.3458, 1.5636, 1.5439, 0.6952, -0.3766
SalesTable:
LOAD recno() as CustID, * inline [
SalesValue
101
163
126
139
167
86
83
22
32
70
108
124
176
113
95
32
42
92
61
21
];

RangeStDev

RangeStdev() finds the standard deviation of a range of numbers.

RangeStdev(first_expr[, Expression])

Return data type: numeric

The arguments of this function may contain inter-record functions which in themselves return a list of values.

Argument Description
first_expr The expression or field containing the data to be measured.
Expression Optional expressions or fields containing the range of data to be measured.

Limitations: If no numeric value is found, NULL is returned.

Examples Results
RangeStdev(1,2,4) Returns 1.5275252316519
RangeStdev(null()) Returns NULL
RangeStdev (above(SalesValue),0,3)) Returns a sliding standard of the range of three values returned from the above() function calculated on the current row and the two rows above the current row.

Data used in example:

CustID RangeStdev(SalesValue, 0,3))
1-20 -,43.841, 34.192, 18.771, 20.953, 41.138, 47.655, 36.116, 32.716, 25.325,38,000, 27.737, 35.553, 33.650, 42.532, 33.858, 32.146, 25.239, 35.595
SalesTable:
LOAD recno() as CustID, * inline [
SalesValue
101
163
126
139
167
86
83
22
32
70
108
124
176
113
95
32
42
92
61
21
];

RangeSum

RangeSum() returns the sum of a range of values.All non-numeric values are treated as 0, unlike the + operator.

RangeSum(first_expr[, Expression])

Return data type: numeric

The arguments of this function may contain inter-record functions which in themselves return a list of values.

Argument Description
first_expr The expression or field containing the data to be measured.
Expression Optional expressions or fields containing the range of data to be measured.

Limitations: The RangeSumfunction treats all non-numeric values as 0, unlike the + operator.

Examples Results
RangeSum(1,2,4) Returns 7
RangeSum(5,'abc') Returns 5
RangeSum(null()) Returns 0
RangeTab3:
LOAD recno() as RangeID,
RangeSum(Field1,Field2,Field3) as MyRangeSum;
INLINE [
Field1,Field2,Field3
10,5,6
2,3,7
8,2,8
18,11,9
5,5,9
9,4,2
];
The resulting table shows the returned values of MyRangeMode for each of the records in the table.
RangeID MyRangeSum
1 21
2 12
3 18
4 38
5 19
6 15

Example with expression:

RangeSum(Above(MyField,0,3))

Returns the sum of the three values of MyField): from the current row and two rows above the current row. By specifying the third argument as 3, the Above() function returns three values, where there are sufficient rows above, which are taken as input to the RangeSum() function.

Data used in examples: | MyField | RangeSum(Above(MyField,0,3)) | | - | - | | 10 | 10 | | 2 | 12 | | 8 | 20 | | 18 | 28 | | 5 | 31 | | 9 | 32 |

RangeTab:
LOAD * INLINE [
MyField
10
2
8
18
5
9
] ;

RangeTextCount

RangeTextCount() returns the number of text values in an expression or field.

RangeTextCount(first_expr[, Expression])

Return data type: numeric

The arguments of this function may contain inter-record functions which in themselves return a list of values.

Argument Description
first_expr The expression or field containing the data to be measured.
Expression Optional expressions or fields containing the range of data to be measured.

Examples: | Examples | Results | | -------- | ------- | | RangeTextCount(1,2,4) | Returns 0 | | RangeTextCount(5,'abc')| Returns 1 | | RangeTextCount(null()) | Returns 0 |

Example with expression:

RangeTextCount(Above(MaxString(MyField),0,3))

Returns the number of text values within the three results of the MaxString(MyField) function evaluated over the current row and two rows above the current row.

Data used in examples:

MyField MaxString(MyField) RangeTextCount(Above(Sum(MyField),0,3))
10 10 0
abc abc 1
8 8 1
def def 2
xyz xyz 2
9 9 2

Data used in examples:

RangeTab:
LOAD * INLINE [
MyField
10
'abc'
8
null()
'xyz'
9
];

RangeXIRR

RangeXIRR() returns the internal rate of return for a schedule of cash flows that is not necessarily periodic. To calculate the internal rate of return for a series of periodic cash flows, use the RangeIRR function.

RangeXIRR(value,date{, value,date})

Return data type: numeric

Argument Description
value A cash flow or a series of cash flows that correspond to a schedule of payments in dates. The series of values must contain at least one positive and one negative value.
date A payment date or a schedule of payment dates that corresponds to the cash flow payments.
Limitations:

Text values, NULL values and missing values are disregarded.
All payments are discounted based on a 365-day year.

Examples Results
RangeXIRR(-2500,'2008-01-01',2750,'2008-09-01') Returns 0.1532

RangeXNPV

RangeXNPV() returns the net present value for a schedule of cash flows that is not necessarily periodic. The result has a default number format of money. To calculate the net present value for a series of periodic cash flows, use the RangeNPV function.

RangeXNPV(discount_rate,values, dates[,Expression])

Return data type: numeric

Argument Description
discount_rate The interest rate per period.
values A cash flow or a series of cash flows that corresponds to a schedule of payments in dates. Each value may be a single value or a range of values as returned by an inter-record function with a third optional parameter. The series of values must contain at least one positive and one negative value.
dates A payment date or a schedule of payment dates that corresponds to the cash flow payments.

Limitations:

Text values, NULL values and missing values are disregarded.

All payments are discounted based on a 365-day year.

Examples Results
RangeXNPV(0.1, -2500,'2008-01-01',2750,'2008-09-01') Returns 80.25
Add the example script to your app and run it.
RangeTab3: LOAD *,
recno() as RangeID,
RangeXNPV(Field1,Field2,Field3) as RangeNPV;
LOAD * INLINE [
Field1|Field2|Field3
10|5|-6000
2|NULL|7000
8|'abc'|8000
18|11|9000
5|5|9000
9|4|2000
] (delimiter is '|');
The resulting table shows the returned values of RangeXNPV for each of the records in the table.
RangeID RangeXNPV
1 $-49.13
2 $777.78
3 $98.77
4 $25.51
5 $250.83
6 $20.40