Amazon QuickSight – varpIf

The Amazon QuickSight VARP_IF function calculates the variance of a population, for a given measure, based on a set of conditions or filters. This allows you to calculate the variance for a specific subset of data within your dataset.

Syntax

#Start#
varpIf(measure, conditions)
#End#

This function takes the following arguments:

• measure: This is a required parameter that specifies the measure for which you want to calculate the variance. It can be a field, an expression, or a calculated field.

• conditions: This is a required parameter that specifies the conditions or filters that you want to apply to the data. You can use any valid expression that evaluates to a Boolean value, such as a comparison or logical operator, or a function that returns a Boolean value.

Suppose you have a dataset that contains sales data for a company, including the sales amount and the region where each sale was made. You want to calculate the variance of the sales amount for all sales made in the “West” region. To do this, you can use the following VARP_IF function:

Example

#Start#
VARP_IF(sales_amount, region = "West")
#End#

In this example, “sales_amount” is the measure for which you want to calculate the variance, and “region” is the field that contains the region where each sale was made. The condition “region = ‘West'” filters the data to only include sales made in the “West” region, and the function will return the variance of the sales amount for those sales.

You can also use multiple conditions in the VARP_IF function by combining them with logical operators. For example, to calculate the variance of sales amounts for sales made in the “West” region and with a sales date in the year 2022, you can use the following function:

Example

#Start#
VARP_IF(sales_amount, region = "West" AND YEAR(sales_date) = 2022)
#End#

In this example, the conditions “region = ‘West'” and “YEAR(sales_date) = 2022” are combined with the logical operator “AND” to filter the data to only include sales made in the “West” region in the year 2022. The function will return the variance of the sales amount for those sales.