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 an elastic mapping.

Use only in mapping tasks.

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. |

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.

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).

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.

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.

The following expression calculates the variance of all rows in the TOTAL_SALES field:

VARIANCE( TOTAL_SALES )

TOTAL_SALES |
---|

2198.0 |

2256.0 |

3001.0 |

NULL |

8953.0 |

RETURN VALUE: 10592444.6666667 |

© Copyright Informatica LLC 2007, 2021