Returns the variance of a value you pass to it. VARIANCE is used to analyze statistical data.
You can nest only one other aggregate function within VARIANCE, and the nested function must return a numeric data type. You cannot nest aggregate functions in advanced mode.
Use only in mapping tasks.
Syntax
VARIANCE( numeric_value [, filter_condition ] )
Argument
Required/
Optional
Description
numeric_value
Required
Numeric data type. Passes the values for which you want to calculate a variance. You can enter any valid transformation expression.
filter_condition
Optional
Limits the rows in the search. The filter condition must be a numeric value or evaluate to TRUE, FALSE, or NULL. You can enter any valid transformation expression.
Formula
The function uses the following formula to calculate the variance:
Use the following guidelines for this formula:
•xi is one of the numeric values.
•n is the number of elements in the set of numeric values. If n is 1, the variance is 0.
Return Value
Double value.
NULL if all values passed to the function are NULL or if no rows are selected (for example, the filter_condition evaluates to FALSE or NULL for all rows).
Nulls
If a single value is NULL, VARIANCE ignores it. However, if all values passed to the function are NULL or if no rows are selected, VARIANCE returns NULL.
Group By
VARIANCE groups values based on group by fields you define in the transformation, returning one result for each group.
If there is not a group by field, VARIANCE treats all rows as one group, returning one value.
Example
The following expression calculates the variance of all rows in the TOTAL_SALES field:
