Amazon QuickSight – var

The var() function in Amazon QuickSight is used to calculate the variance of a measure in a dataset. Variance is a statistical measure that indicates how much the values in a dataset vary from the mean value.

Syntax

#Start#
var(measure, [group-by level])
#End#

This function takes the following arguments:

measure: This parameter specifies the measure for which you want to calculate the variance. The measure can be a field or an expression.

[group-by level] (optional): This parameter specifies the level of aggregation you want to use to calculate the variance. If you specify a group-by level, the variance is calculated for each group separately. If you do not specify a group-by level, the variance is calculated for the entire dataset.

Suppose you have a sales dataset with the following fields: Date, Product, Sales, and Region. You want to calculate the variance of sales for the entire dataset. Here’s an example formula:

Example

#Start#
var(Sales)
#End#

This formula calculates the variance of the Sales field for the entire dataset.

You can also use the var() function with a group-by level to calculate the variance for each group separately. For example, suppose you want to calculate the variance of sales for each product category. Here’s an example formula:

Example

#Start#
var(Sales, Product)
#End#

This formula calculates the variance of the Sales field for each distinct value in the Product field. The Product field is used as the group-by level.

You can also use the var() function with other functions and expressions to calculate more complex calculations. For example, you could calculate the variance of sales values that are greater than the average sales value, using the following formula:

Example

#Start#
var(ifelse(Sales > avg(Sales), Sales, null))
#End#

This formula uses the ifelse() function to replace the sales values that are less than or equal to the average sales value with null. The var() function is then used to calculate the variance of the sales values that are greater than the average sales value.