Statistical Aggregation functions¶
Avg¶
Avg() finds the average value of the aggregated data in the expression over a number of records as defined by a group by clause.
Avg( [DISTINCT] expr )
Return data type: numeric
Argument  Description 

expr  The expression or field containing the data to be measured. 
DISTINCT  If the word distinct occurs before the expression, all duplicates will be disregarded. 
Add the example script to your app and run it. Then add, at least, the fields listed in the results column to a sheet in your app to see the result.
Example  Result 

Temp:

Customer MyAverageSalesByCustomer Astrida 48.916667 Betacab 44.916667 Canutility 56.916667 Divadip 63.083333 This can be checked in the sheet by creating a table including the measure: Sum(Sales)/12 
Given that the Temp table is loaded as in the previous example:LOAD Customer,Avg(DISTINCT Sales) as MyAvgSalesDistinct 
Customer MyAverageSalesByCustomer 
Correl¶
Correl() returns the aggregated correlation coefficient for a series of coordinates represented by paired numbers in xexpression and yexpression iterated over a number of records as defined by a group by clause.
Correl( value1, value2 )
Return data type: numeric
Argument  Description 

value1, value2  The expressions or fields containing the two sample sets for which the correlation coefficient is to be measured. 
Text values, NULL values and missing values in any or both pieces of a datapair result in the entire datapair being disregarded.
Add the example script to your app and run it. Then add, at least, the fields listed in the results column to a sheet in your app to see the result.
Example  Result 

Salary: 
In a table with the dimension Correl_Salary, the result of the Correl() calculation in the data load script will be shown: 0.9270611 
Fractile¶
Fractile() finds the value that corresponds to the fractile (quantile) of the aggregated data in the expression over a number of records as defined by a group by clause.
`Fractile( expr, fraction )``
Return data type: numeric
Argument  Description 

expr  The expression or field containing the data to be measured. 
fraction  A number between 0 and 1 corresponding to the fractile (quantile expressed as a fraction) to be calculated. 
Add the example script to your app and run it. Then add, at least, the fields listed in the results column to a sheet in your app to see the result.
Example  Result 

Table1:
crosstable LOAD recno() as ID, * inline [ 
In a table with the dimensions Type and MyFractile, the results of the Fractile()
calculations in the data load script are:
Type MyFractile 
Kurtosis¶
Kurtosis() returns the kurtosis of the data in the expression over a number of records as defined by a group by clause.
Kurtosis( [distinct ] expr )
Return data type: numeric
Argument  Description 

expr  The expression or field containing the data to be measured. 
distinct  If the word distinct occurs before the expression, all duplicates will be disregarded. 
Add the example script to your app and run it. Then add, at least, the fields listed in the results column to a sheet in your app to see the result.
Example  Result 

Table1: 
In a table with the dimensions Type, MyKurtosis1,and MyKurtosis2, the results of the Kurtosis()
calculations in the data load script are:
Type MyKurtosis1 MyKurtosis2 
LINEST_B¶
LINEST_B() returns the aggregated b value (yintercept) of a linear regression defined by the equation y=mx+b for a series of coordinates represented by paired numbers in xexpression and yexpression iterated over a number of records as defined by a group by clause.
LINEST_B( y_value, x_value[, y0 [, x0 ]] )
Return data type: numeric
Argument  Description 

y_value  The expression or field containing the range of yvalues to be measured. 
x_value  The expression or field containing the range of xvalues to be measured. 
y(0), x(0)  An optional value y0 may be stated forcing the regression line to pass through the yaxis at a given point. By stating both y0 and x0 it is possible to force the regression line to pass through a single fixed coordinate. Unless both y0 and x0 are stated, the function requires at least two valid datapairs to calculate. If y0 and x0 are stated, a single data pair will do. 
Text values, NULL values and missing values in any or both pieces of a datapair result in the entire datapair being disregarded.
LINEST_DF¶
LINEST_DF() returns the aggregated degrees of freedom of a linear regression defined by the equation y=mx+b for a series of coordinates represented by paired numbers in xexpression and yexpression iterated over a number of records as defined by a group by clause.
LINEST_DF( y_value, x_value[, y0 [, x0 ]] )
Return data type: numeric
Argument  Description 

y_value  The expression or field containing the range of yvalues to be measured. 
x_value  The expression or field containing the range of xvalues to be measured. 
y(0), x(0)  An optional value y0 may be stated forcing the regression line to pass through the yaxis at a given point. By stating both y0 and x0 it is possible to force the regression line to pass through a single fixed coordinate. Unless both y0 and x0 are stated, the function requires at least two valid datapairs to calculate. If y0 and x0 are stated, a single data pair will do. 
Text values, NULL values and missing values in any or both pieces of a datapair result in the entire datapair being disregarded.
LINEST_F¶
This script function returns the aggregated F statistic (r^{2}/(1r^{2})) of a linear regression defined by the equation y=mx+b for a series of coordinates represented by paired numbers in xexpression and yexpression iterated over a number of records as defined by a group by clause.
LINEST_F( y_value, x_value[, y0 [, x0 ]] )
Return data type: numeric
Argument  Description 

y_value  The expression or field containing the range of yvalues to be measured. 
x_value  The expression or field containing the range of xvalues to be measured. 
y(0), x(0)  An optional value y0 may be stated forcing the regression line to pass through the yaxis at a given point. By stating both y0 and x0 it is possible to force the regression line to pass through a single fixed coordinate. Unless both y0 and x0 are stated, the function requires at least two valid datapairs to calculate. If y0 and x0 are stated, a single data pair will do. 
Text values, NULL values and missing values in any or both pieces of a datapair result in the entire datapair being disregarded.
LINEST_M¶
LINEST_M() returns the aggregated m value (slope) of a linear regression defined by the equation y=mx+b for a series of coordinates represented by paired numbers in xexpression and yexpression iterated over a number of records as defined by a group by clause.
LINEST_M( y_value, x_value[, y0 [, x0 ]] )
Return data type: numeric
Argument  Description 

y_value  The expression or field containing the range of yvalues to be measured. 
x_value  The expression or field containing the range of xvalues to be measured. 
y(0), x(0)  An optional value y0 may be stated forcing the regression line to pass through the yaxis at a given point. By stating both y0 and x0 it is possible to force the regression line to pass through a single fixed coordinate. Unless both y0 and x0 are stated, the function requires at least two valid datapairs to calculate. If y0 and x0 are stated, a single data pair will do. 
Text values, NULL values and missing values in any or both pieces of a datapair result in the entire datapair being disregarded.
LINEST_R2¶
LINEST_R2() returns the aggregated r^{2} value (coefficient of determination) of a linear regression defined by the equation y=mx+b for a series of coordinates represented by paired numbers in xexpression and yexpression iterated over a number of records as defined by a group by clause.
LINEST_R2( y_value, x_value[, y0 [, x0 ]] )
Return data type: numeric
Argument  Description 

y_value  The expression or field containing the range of yvalues to be measured. 
x_value  The expression or field containing the range of xvalues to be measured. 
y(0), x(0)  An optional value y0 may be stated forcing the regression line to pass through the yaxis at a given point. By stating both y0 and x0 it is possible to force the regression line to pass through a single fixed coordinate. Unless both y0 and x0 are stated, the function requires at least two valid datapairs to calculate. If y0 and x0 are stated, a single data pair will do. 
Text values, NULL values and missing values in any or both pieces of a datapair result in the entire datapair being disregarded.
LINEST_SEB¶
LINEST_SEB() returns the aggregated standard error of the b value of a linear regression defined by the equation y=mx+b for a series of coordinates represented by paired numbers in xexpression and yexpression iterated over a number of records as defined by a group by clause.
LINEST_SEB( y_value, x_value[, y0 [, x0 ]] )
Return data type: numeric
Argument  Description 

y_value  The expression or field containing the range of yvalues to be measured. 
x_value  The expression or field containing the range of xvalues to be measured. 
y(0), x(0)  An optional value y0 may be stated forcing the regression line to pass through the yaxis at a given point. By stating both y0 and x0 it is possible to force the regression line to pass through a single fixed coordinate. Unless both y0 and x0 are stated, the function requires at least two valid datapairs to calculate. If y0 and x0 are stated, a single data pair will do. 
Text values, NULL values and missing values in any or both pieces of a datapair result in the entire datapair being disregarded.
LINEST_SEM¶
LINEST_SEM() returns the aggregated standard error of the m value of a linear regression defined by the equation y=mx+b for a series of coordinates represented by paired numbers in xexpression and yexpression iterated over a number of records as defined by a group by clause.
LINEST_SEM( y_value, x_value[, y0 [, x0 ]] )
Return data type: numeric
Argument  Description 

y_value  The expression or field containing the range of yvalues to be measured. 
x_value  The expression or field containing the range of xvalues to be measured. 
y(0), x(0)  An optional value y0 may be stated forcing the regression line to pass through the yaxis at a given point. By stating both y0 and x0 it is possible to force the regression line to pass through a single fixed coordinate. Unless both y0 and x0 are stated, the function requires at least two valid datapairs to calculate. If y0 and x0 are stated, a single data pair will do. 
Text values, NULL values and missing values in any or both pieces of a datapair result in the entire datapair being disregarded.
LINEST_SEY¶
LINEST_SEY() returns the aggregated standard error of the y estimate of a linear regression defined by the equation y=mx+b for a series of coordinates represented by paired numbers in xexpression and yexpression iterated over a number of records as defined by a group by clause.
LINEST_SEY( y_value, x_value[, y0 [, x0 ]] )
Return data type: numeric
Argument  Description 

y_value  The expression or field containing the range of yvalues to be measured. 
x_value  The expression or field containing the range of xvalues to be measured. 
y(0), x(0)  An optional value y0 may be stated forcing the regression line to pass through the yaxis at a given point. By stating both y0 and x0 it is possible to force the regression line to pass through a single fixed coordinate. Unless both y0 and x0 are stated, the function requires at least two valid datapairs to calculate. If y0 and x0 are stated, a single data pair will do. 
Text values, NULL values and missing values in any or both pieces of a datapair result in the entire datapair being disregarded.
LINEST_SSREG¶
LINEST_SSREG() returns the aggregated regression sum of squares of a linear regression defined by the equation y=mx+b for a series of coordinates represented by paired numbers in xexpression and yexpression iterated over a number of records as defined by a group by clause.
LINEST_SSREG( y_value, x_value[, y0 [, x0 ]] )
Return data type: numeric
Argument  Description 

y_value  The expression or field containing the range of yvalues to be measured. 
x_value  The expression or field containing the range of xvalues to be measured. 
y(0), x(0)  An optional value y0 may be stated forcing the regression line to pass through the yaxis at a given point. By stating both y0 and x0 it is possible to force the regression line to pass through a single fixed coordinate. Unless both y0 and x0 are stated, the function requires at least two valid datapairs to calculate. If y0 and x0 are stated, a single data pair will do. 
Text values, NULL values and missing values in any or both pieces of a datapair result in the entire datapair being disregarded.
LINEST_SSRESID¶
LINEST_SSRESID() returns the aggregated residual sum of squares of a linear regression defined by the equation y=mx+b for a series of coordinates represented by paired numbers in xexpression and yexpression iterated over a number of records as defined by a group by clause.
LINEST_SSRESID( y_value, x_value[, y0 [, x0 ]] )
Return data type: numeric
Argument  Description 

y_value  The expression or field containing the range of yvalues to be measured. 
x_value  The expression or field containing the range of xvalues to be measured. 
y(0), x(0)  An optional value y0 may be stated forcing the regression line to pass through the yaxis at a given point. By stating both y0 and x0 it is possible to force the regression line to pass through a single fixed coordinate. Unless both y0 and x0 are stated, the function requires at least two valid datapairs to calculate. If y0 and x0 are stated, a single data pair will do. 
Text values, NULL values and missing values in any or both pieces of a datapair result in the entire datapair being disregarded.
Median¶
Median() returns the aggregated median of the values in the expression over a number of records as defined by a group by clause.
Median( expr )
Return data type: numeric
Argument  Description 

expr  The expression or field containing the data to be measured. 
Add the example script to your app and run it. Then build a straight table with Type and MyMedian as dimensions.
Example  Result 

Table1:

The results of the Median() calculation are:

Skew¶
Skew() returns the skewness of expression over a number of records as defined by a group by clause.
Skew( [ distinct] expr )
Return data type: numeric
Argument  Description 

expr  The expression or field containing the data to be measured. 
DISTINCT  If the word distinct occurs before the expression, all duplicates will be disregarded. 
Add the example script to your app and run it. Then build a straight table with Type and MySkew as dimensions.
Example  Result 

Table1: 
The results of the Skew() calculation are:

Stdev¶
Stdev() returns the standard deviation of the values given by the expression over a number of records as defined by a group by clause.
Stdev( [distinct] expr )
Return data type: numeric
Argument  Description 

expr  The expression or field containing the data to be measured. 
distinct  If the word distinct occurs before the expression, all duplicates will be disregarded. 
Add the example script to your app and run it. Then build a straight table with Type and MyStdev as dimensions.
Example  Result 

Table1: 
The results of the Stdev() calculation are:

Sterr¶
Sterr() returns the aggregated standard error (stdev/sqrt(n)) for a series of values represented by the expression iterated over a number of records as defined by a group by clause.
Sterr( [distinct] expr )
Return data type: numeric
Argument  Description 

expr  The expression or field containing the data to be measured. 
distinct  If the word distinct occurs before the expression, all duplicates will be disregarded. 
Text values, NULL values and missing values are disregarded.
Add the example script to your app and run it. Then add, at least, the fields listed in the results column to a sheet in your app to see the result.
Example  Result 

Table1: 
In a table with the dimensions Type and MySterr, the results of the Sterr() calculation in the data load script are: Type MySterr Comparison 3.2674431 Observation 2.7968733 
STEYX¶
STEYX() returns the aggregated standard error of the predicted yvalue for each xvalue in the regression for a series of coordinates represented by paired numbers in xexpression and yexpression iterated over a number of records as defined by a group by clause.
`STEYX( y_value, x_value )``
Return data type: numeric
Argument  Description 

y_value  The expression or field containing the range of yvalues to be measured. 
x_value  The expression or field containing the range of xvalues to be measured. 
Text values, NULL values and missing values in any or both pieces of a datapair result in the entire datapair being disregarded.
Add the example script to your app and run it. Then add, at least, the fields listed in the results column to a sheet in your app to see the result.
Example  Result 

Trend: 
In a table with the dimension MySTEYX, the result of the STEYX() calculation in the data load script is 2.0714764. 