Statistical functions

The list shows the statistical functions that you can use in expressions.


Function	Description
`AndersonDarlingTest(Arg1)`	Calculates the test statistic with an Anderson-Darling test. Example: `AndersonDarlingTest([Value column])`
`AndersonDarlingTestP(Arg1)`	Calculates the p-value for an Anderson-Darling test. Example: `AndersonDarlingTestP([Value column])`
`ANOVAOneWay(Arg1, Arg2)`	Calculates the one-way ANOVA test statistic for input data. The category can be a string or an integer column. Example: `ANOVAOneWay([Value column], [Category])`
`ANOVAOneWayP(Arg1, Arg2)`	Calculates the p-value for a one-way ANOVA test. The category can be a string or an integer column. Example: `ANOVAOneWayP([Value column], [Category])`
`Avg(Arg1, ...)`	Returns the average (arithmetic mean) of the arguments. The arguments and the result are of type real. If one argument is given, then the result is the average of all rows. If more than one argument is given, then the result is the average for each row. Null arguments are ignored and do not contribute to the average. Examples: `Avg([Column])` `Avg(2,-3,4)`→ 1 `Avg(-1)` → -1 `Avg(1.5, -2, 3.5)` → 1 `Avg(1, null, 3)` → 2 `Avg(null)` → `(Empty)`
`BetaDist(Arg1, Arg2, Arg3)`	Returns the (upper tail) Beta p-value of the argument. The first argument is the quantile, the second argument is the shape parameter alpha, and the third argument is the shape parameter beta. Example: `BetaDist(0.25, 2, 4)` → 0.632813
`BetaInv(Arg1, Arg2, Arg3)`	Returns the (upper tail) Beta quantile value of the argument. The first argument is the p-value, the second argument is the shape parameter alpha, and the third argument is the shape parameter beta. Example: `BetaInv(0.5, 4, 2)` → 0.686190
`BrownForsytheTest(Arg1, Arg2)`	Calculates the H-statistic with a Brown-Forsythe test for the input data. The category can be a string or an integer column. Example: `BrownForsytheTest([Value column], [Category])`
`BrownForsytheTestP(Arg1, Arg2)`	Calculates the p-value with a Brown-Forsythe test for the input data. The category can be a string or an integer column. Example: `BrownForsytheTestP([Value column], [Category])`
`ChiDist(Arg1, Arg2)`	Returns the (upper tail) chi-square p-value of the argument. The first argument is the quantile, and the second argument is the number of degrees of freedom. Examples: `ChiDist(x, deg_freedom)` `ChiDist(7.377759, 2)` → 0.025
`ChiInv(Arg1, Arg2)`	Returns the (upper tail) chi-square quantile value of the argument. The first argument is the p-value, and the second argument is the number of degrees of freedom. Examples: `ChiInv(p, deg_freedom)` `ChiInv(0.025, 2)` → 7.377759
`Count(Arg1)`	Calculates the number of non-empty values in the argument column, or, if no argument is specified, the total number of rows. Example: `Count([Column])`
`CountBig(Arg1)`	Calculates the number of non-empty values in the argument column, or, if no argument is specified, the total number of rows. This function returns a LongInteger. Example: `CountBig([Column])`
`Covariance(Arg1, Arg2)`	Calculates the covariance of two columns given as arguments. Example: `Covariance([Column1], [Column2])`
`FDist(Arg1, Arg2, Arg3)`	Returns the upper tail F p-value of the argument. The first argument is the quantile, the second argument is the degrees of freedom m (numerator) and the third argument is the degrees of freedom n (denominator). Examples: `FDist(x, deg_freedom1, deg_freedom2)` `FDist(6.936728, 1, 10)` → 0.025
`FInv(Arg1, Arg2, Arg3)`	Returns the (upper tail) F quantile value of the argument. The first argument is the p-value, the second argument is the degrees of freedom m (numerator) and the third argument is the degrees of freedom n (denominator). Examples: `FInv(p, deg_freedom1, deg_freedom2)` `FInv(0.025, 1, 10)` → 6.936728
`First(Arg1)`	Returns the first valid value based on the physical order of the rows of data in the argument column. Example: `First([Column])`
`GeometricMean()`	Calculates the geometric mean value. If any input value is negative then the result will be "Empty". If any input value is equal to zero then the result will be zero. Example: `GeometricMean([Sales])`
`IQR(Arg1)`	Calculates the value difference Q3-Q1, or, the 75th percentile minus the 25th percentile. IQR is also referred to as the H-spread. Example: `IQR([Column])`
`KruskalWallisTest(Arg1, Arg2)`	Calculates the H-statistic with a Kruskal-Wallis test for the input data. The category can be a string or an integer column. Example: `KruskalWallisTest([Value column], [Category])`
`KruskalWallisTestP(Arg1, Arg2)`	Calculates the p-value with a Kruskal-Wallis test for the input data. The category can be a string or an integer column. Example: `KruskalWallisTestP([Value column], [Category])`
`KSDist(Arg1, Arg2)`	Returns the (upper tail) Kolmogorov-Smirnov p-value of the argument. The first argument is the quantile (distance statistic), and the second argument is the number of observations. Example: `KSDist(0.5, 2)` → 0.699374
`KSInv(Arg1, Arg2)`	Returns the (upper tail) Kolmogorov-Smirnov quantile value of the argument. In other words, the distance for when this probability is observed. The first argument is the p-value, and the second argument is the number of observations. Example: `KSInv(0.5, 4)` → 0.413787
`KSTest(Arg1)`	Calculates the Kolmogorov-Smirnov statistic for a column against a normal distribution. The mean and the standard deviation of the normal distribution are estimated based on the data in the column. Example: `KSTest([Value column])`
`KSTestP(Arg1)`	Calculates the Kolmogorov-Smirnov p-value for a column against a normal distribution. The mean and the standard deviation of the normal distribution are estimated based on the data in the column. Example: `KSTestP([Value column])`
`KSTestTwo(Arg1, Arg2)`	Calculates the two-sample Kolmogorov-Smirnov test statistic for a column against a reference. The second column can be an integer or string to indicate the category the data is in. It can also be a boolean column indicating whether a data point is in the reference sample. Examples: `KSTestTwo([Value column], [Category])` `KSTestTwo([Value column], [Category]=1)`
`KSTestTwoP(Arg1, Arg2)`	Calculates the p-value for the two-sample Kolmogorov-Smirnov test for a column against a reference. The second column can be an integer or string to indicate the category the data is in. It can also be a boolean column indicating whether a data point is in the reference sample. Examples: `KSTestTwoP([Value column], [Category])` `KSTestTwoP([Value column], [Category]=1)`
`Kurtosis(Arg1, Arg2)`	Calculates the sample excess kurtosis of a column. The second optional parameter specifies whether to correct statistical bias (default is true). Examples: `Kurtosis([Value column])` `Kurtosis([Value column], false)`
`L95(Arg1)`	Calculates the lower endpoint of the 95% confidence interval. Note: The static t-value of 1.959964, used by this function, is adapted for large sample sizes (`n >= 40`). For smaller sample sizes, use the following expression instead: `Avg([Value])-TInv(0.025,Count()-1)*StdDev([Value])/Sqrt(Count())` Example: `L95([Column])`
`Lag(Arg1, Arg2)`	Shifts the values in a column downward by a specified number of steps. The first argument is the column to shift. The second (optional) argument is the number of steps. Default is 1. If a negative number of steps is used, then the values are shifted in the opposite direction, see image below. Examples: `Lag([Column])` `Lag([Column],3)` Note that the Lag function is applied to the data in the order the data was loaded; the function does not take sorting in visualizations into account and any changes to the data (e.g., during reload) might result in different values for the various rows.
`Last(Arg1)`	Returns the last valid value based on the physical order of the rows of data in the argument column. Example: `Last([Column])`
`LastValueForMax(Arg1, Arg2)`	Returns the value of column 2 for the maximum value of column 1. If there are more than one of the column 1 maximum value, then the result will be the value for the last max row. See also `ValueForMax`. Example: `LastValueForMax([Column 1], [Column 2])`
`LastValueForMin(Arg1, Arg2)`	Returns the value of column 2 for the minimum value of column 1. If there are more than one of the column 1 minimum value, then the result will be the value for the last min row. See also `ValueForMin`. Example: `LastValueForMin([Column 1], [Column 2])`
`LAV(Arg1)`	Calculates the lower adjacent value. Example: `LAV([Column])`
`Lead(Arg1, Arg2)`	Shifts the values in a column upward by a specified number of steps. The first argument is the column to shift. The second (optional) argument is the number of steps. Default is 1. If a negative number of steps is used, then the values are shifted in the opposite direction, see image below. Examples: `Lead([Column])` `Lead([Column],3)` Note that the Lead function is applied to the data in the order the data was loaded; the function does not take sorting in visualizations into account and any changes to the data (e.g., during reload) might result in different values for the various rows.
`LevenesTest(Arg1, Arg2)`	Calculates the H-statistic with a Levene's test for the input data. The category can be a string or an integer column. Example: `LevenesTest([Value column], [Category])`
`LevenesTestP(Arg1, Arg2)`	Calculates the p-value with a Levene's test for the input data. The category can be a string or an integer column. Example: `LevenesTestP([Value column], [Category])`
`LIF(Arg1)`	Calculates the lower inner fence. This is the threshold located at `Q1 - (1.5*IQR)`. Example: `LIF([Column])`
`LOF(Arg1)`	Calculates the lower outer fence. This is the threshold located at `Q1 - (3*IQR)`. Example: `LOF([Column])`
`Max(Arg1, ...)`	Calculates the maximum value. If one argument is given, then the result is the maximum for the entire column. If more than one argument is given, then the result is the maximum for each row. The argument and the result are of type real. Null arguments are ignored. Examples: `Max([Column])` `Max(-1)`→ -1 `Max (1.5, -2, 3)` → 3 `Max (1, null, 3)` → 3 `Max (null)` → `(Empty)`
`MeanDeviation(Arg1, ...)`	Calculates the mean deviation value (average absolute deviation, AAD). If one argument is given, then the result is the mean deviation of all rows. If more than one argument is given, then the result is the mean deviation for each row. Examples: `MeanDeviation([Column])` `MeanDeviation(2,-3,4)`→ 2.67
`Median(Arg1)`	Calculates the median of the argument. If one argument is given, then the result is the median of all rows. If more than one argument is given, then the result is the median for each row. Examples: `Median([Column])` `Median(2,-3,4)`
`MedianAbsoluteDeviation(Arg1, ...)`	Calculates the median absolute deviation (MAD). If one argument is given, then the result is the median absolute deviation of all rows. If more than one argument is given, then the result is the median absolute deviation for each row. Examples: `MedianAbsoluteDeviation([Sales])` `MedianAbsoluteDeviation(2,-3,4)`
`Min(Arg1, ...)`	Calculates the minimum value. If one argument is given, then the result is the minimum for the entire column. If more than one argument is given, then the result is the minimum for each row. The argument and the result are of type real. Null arguments are ignored. Examples: `Min([Column])` `Min(-1)`→ -1 `Min (1.5, -2, 3)` → -2 `Min (1, null, 3)` → 1 `Min (null)`→ `(Empty)`
`NormDist(Arg1, Arg2, Arg3)`	Returns the (upper tail) normal p-value of the argument. The first argument is the quantile, the second (optional) argument is the mean and the third (optional) argument is the standard deviation. If not specified, mean = 0 and standard_dev = 1, that is, the standard normal (Gaussian) distribution. Examples: `NormDist(x, mean, standard_dev)` `NormDist(1.96)` → 0.025
`NormInv(Arg1, Arg2, Arg3)`	Returns the (upper tail) normal quantile value of the argument. The first argument is the p-value, the second (optional) argument is the mean and the third (optional) argument is the standard deviation. If not specified, mean = 0 and standard_dev = 1, that is, the standard normal (Gaussian) distribution. Examples: `NormInv(p, mean, standard_dev)` `NormInv(0.025)` → 1.96
`NthLargest(Arg1, Arg2)`	The nth largest value. The first argument is the column to analyze and the second argument is the value of n. If n is larger than the number of values in the column, then the smallest value is returned. Example: `NthLargest([Column], 10)`
`NthSmallest(Arg1, Arg2)`	The nth smallest value. The first argument is the column to analyze and the second argument is the value of n. If n is larger than the number of values in the column, then the largest value is returned. Example: `NthSmallest([Column], 10)`
`Outliers(Arg1)`	Outer value count. Calculates the count of values that are greater than the upper adjacent value or lower than the lower adjacent value. Example: `Outliers([Column])`
`P10(Arg1)`	The 10th percentile is the value at which 10 percent of the data values are equal to or lower than the value. Example: `P10([Column])`
`P90(Arg1)`	The 90th percentile is the value at which 90 percent of the data values are equal to or lower than the value. Example: `P90([Column])`
`PctOutliers(Arg1)`	Outer value percentile. Calculates the percent of values that are greater than the upper adjacent value or lower than the lower adjacent value. Example: `PctOutliers([Column])`
`Percent(Arg1, Arg2)`	The percent is the value calculated a certain percent above the minimum value within the value range (max value - min value). The first argument is the column to analyze and the second argument is the percent. Example: `Percent([Column], 15.0)`
`Percentile(Arg1, Arg2)`	The percentiles is the value at which a certain percent of the data values are equal to or lower than the value. The first argument is the column to analyze and the second argument is the percent. Example: `Percentile([Column], 15.0)`
`Q1(Arg1)`	Calculates the first quartile. Example: `Q1([Column])`
`Q3(Arg1)`	Calculates the third quartile. Example: `Q3([Column])`
`Range(Arg1)`	The range between the largest and the smallest value in the column. The result will be presented as a real or a timespan, depending on the data type of the argument. Example: `Range([Column])`
`ShapiroWilk(Arg1)`	Calculates the Shapiro-Wilk statistic for a column. Should not be used on columns with more than 5000 values. Example: `ShapiroWilk([Value column])`
`ShapiroWilkP(Arg1)`	Calculates the Shapiro-Wilk p-value for a column. Should not be used on columns with more than 5000 values. Example: `ShapiroWilkP([Value column])`
`Skewness(Arg1, Arg2)`	Calculates the Fisher-Pearson coefficient of skewness for a column. The second optional parameter is a boolean, which states whether to correct for statistical bias (default is true). Examples: `Skewness([Value column])` `Skewness([Value column], false)`
`StdDev(Arg1)`	Calculates the standard deviation. Example: `StdDev([Column])`
`StdErr(Arg1)`	Calculates the standard error. Example: `StdErr([Column])`
`TDist(Arg1, Arg2)`	Returns the (upper tail) t p-value of the argument. The first argument is the quantile, and the second argument is the degrees of freedom. Examples: `TDist(x, deg_freedom)` `TDist(4.302653, 2)` → 0.025
`TERR_Binary`	Calls the Spotfire® Enterprise Runtime for R (a/k/a TERR™) and returns an output of the specified data type, containing the same number of rows as the input. The first argument is a script and the following arguments are the arguments to the script. The column returned must have the same number of rows as the input. At least one argument other than the script is required. The inputs will be placed in variables called `input1`, `input2`, ... `inputN`, and the output must be placed in a variable called `output`. Examples: `TERR_Real("output <- input1*100 + input2", [Record No], [Sales])` `TERR_String("output <- input1", [String Column])`
`TERR_Boolean`	See TERR_Binary above.
`TERR_DateTime`	See TERR_Binary above.
`TERR_Integer`	See TERR_Binary above.
`TERR_Real`	See TERR_Binary above.
`TERR_String`	See TERR_Binary above.
`TERRAggregation_Binary`	Calls the TERR engine and returns an output of the specified data type. The first argument is a script and the following arguments are the arguments to the script. The script should return a single aggregated value. At least one argument other than the script is required. The inputs will be placed in variables called `input1`, `input2`, ... `inputN`, and the output must be placed in a variable called `output`. Examples: `TERRAggregation_Real("output <- median(input1) + median(input2)", [X], [Y])` `TERRAggregation_String("output <- input1[1]", [Customer Name])`
`TERRAggregation_Boolean`	See TERRAggregation_Binary above.
`TERRAggregation_DateTime`	See TERRAggregation_Binary above.
`TERRAggregation_Integer`	See TERRAggregation_Binary above.
`TERRAggregation_Real`	See TERRAggregation_Binary above.
`TERRAggregation_String`	See TERRAggregation_Binary above.
`TInv(Arg1, Arg2)`	Returns the (upper tail) t quantile value of the argument. The first argument is the p-value, and the second argument is the degrees of freedom. Examples: `TInv(p, deg_freedom)` `TInv(0.025, 2)` → 4.302653
`TrimmedMean(Arg1, Arg2)`	Calculates the trimmed mean value (trimmed average). The first argument is the column to analyze and the second argument is, in percent, how many values to exclude from the calculation. If the trim value is set to 10%, then the highest 5% and the lowest 5% of the values are excluded from the calculated mean. Example: `TrimmedMean([Sales], 10)`
`TTestIndependent(Arg1, Arg2, Arg3)`	Calculates the t-statistic with an independent t-test for the input data. Can either be the Student's t-test or Welch's t-test. The second column can be an integer or string to indicate the category the data is in. It can also be a boolean column indicating whether a data point is in the reference sample. The third argument is a boolean value that specifies whether equal variance is assumed (default is false). If the value is set to false, the Welch's t-test will be used, while the Student's t-test is used if the value is set to true. Examples: `TTestIndependent([Value column], [Category])` `TTestIndependent([Value column], [Category]=1)` `TTestIndependent([Value column], [Category], True)`
`TTestIndependentP(Arg1, Arg2, Arg3)`	Calculates the p-value for an independent two-sample t-test for a column with a reference column. Can either be the Student's t-test or Welch's t-test. The second column can be an integer or string to indicate the category the data is in. It can also be a boolean column indicating whether a data point is in the reference sample. The third argument is a boolean value that specifies whether equal variance is assumed (default is false). If the value is set to false, the Welch's t-test will be used, while the Student's t-test is used if the value is set to true. Examples: `TTestIndependentP([Value column], [Category])` `TTestIndependentP([Value column], [Category]=1)` `TTestIndependentP([Value column], [Category], True)`
`U95(Arg1)`	Calculates the upper endpoint of the 95% confidence interval. Note: The static t-value of 1.959964, used by this function, is adapted for large sample sizes (`n >= 40`). For smaller sample sizes, use the following expression instead: `Avg([Value])+TInv(0.025,Count()-1)*StdDev([Value])/Sqrt(Count())` Example: `U95([Column])`
`UAV(Arg1)`	Calculates the upper adjacent value. Example: `UAV([Column])`
`UIF(Arg1)`	Calculates the upper, inner fence. This is the threshold located at `Q3 + (1.5*IQR)`. Example: `UIF([Column])`
`UniqueCount(Arg1)`	Calculates the number of unique, non-empty values in the argument column. Example: `UniqueCount([Column])`
`UOF(Arg1)`	Calculates the upper outer fence. This is the threshold located at Q3 + (3*IQR). Example: `UOF([Column])`
`ValueForMax(Arg1, Arg2)`	Returns the value of column 2 for the maximum value of column 1. If there are more than one of the column 1 maximum value, then the result will be the value for the first max row. See also `LastValueForMax`. Example: `ValueForMax([Column 1], [Column 2])`
`ValueForMin(Arg1, Arg2)`	Returns the value of column 2 for the minimum value of column 1. I f there are more than one of the column 1 minimum value, then the result will be the value for the first min row. See also `LastValueForMin`. Example: `ValueForMin([Column 1], [Column 2])`
`Var(Arg1)`	Calculates the variance. Example: `Var([Column])`
`WeightedAverage(Arg1, Arg2)`	Calculates the weighted average of two columns. Arg1 is the weight column and Arg2 is the value column. Example: `WeightedAverage([Column1],[Column2])`

Tip: The DISTINCT keyword can be used to return a result using the unique values only. For example, Avg(DISTINCT[Column]) would return the average of the unique values rather than the average of all values in the specified column. UniqueCount([Column]) is the equivalent of Count(DISTINCT[Column]).