##### Page tree

Contents:

This example illustrates how to use the conditional calculation functions.

Functions:

ItemDescription
AVERAGEIF Function Generates the average value of rows in each group that meet a specific condition. Generated value is of Decimal type.
MINIF Function Generates the minimum value of rows in each group that meet a specific condition. Inputs can be Integer, Decimal, or Datetime.
MAXIF Function Generates the maximum value of rows in each group that meet a specific condition. Inputs can be Integer, Decimal, or Datetime.
VARIF Function Generates the variance of values by group in a column that meet a specific condition.
STDEVIF Function Generates the standard deviation of values by group in a column that meet a specific condition.

Source:

Here is some example weather data:

datecityraintempwind
1/23/17Valleyville0.0012.86.7
1/23/17Center Town0.319.45.3
1/23/17Magic Mountain0.000.07.3
1/24/17Valleyville0.2517.23.3
1/24/17Center Town0.541.17.6
1/24/17Magic Mountain0.325.08.8
1/25/17Valleyville0.023.36.8
1/25/17Center Town0.833.35.1
1/25/17Magic Mountain0.59-1.76.4
1/26/17Valleyville1.0815.04.2
1/26/17Center Town0.966.17.6
1/26/17Magic Mountain0.77-3.93.0
1/27/17Valleyville1.007.22.8
1/27/17Center Town1.3220.00.2
1/27/17Magic Mountain0.775.65.2
1/28/17Valleyville0.12-6.15.1
1/28/17Center Town0.145.04.9
1/28/17Magic Mountain1.501.10.4
1/29/17Valleyville0.3613.37.3
1/29/17Center Town0.756.19.0
1/29/17Magic Mountain0.603.36.0

Transformation:

The following computes average temperature for rainy days by city:

Transformation Name `New formula` `Single row formula` `AVERAGEIF(temp, rain > 0)` `city` `'avgTempWRain'`

The following computes maximum wind for sub-zero days by city:

Transformation Name `New formula` `Single row formula` `MAXIF(wind,temp < 0)` `city` `'maxWindSubZero'`

This step calculates the minimum temp when the wind is less than 5 mph by city:

Transformation Name `New formula` `Single row formula` `MINIF(temp,wind<5)` `city` `'minTempWind5'`

This step computes the variance in temperature for rainy days by city:

Transformation Name `New formula` `Single row formula` `VARIF(temp,rain >0)` `city` `'varTempWRain'`

The following computes the standard deviation in rainfall for Center Town:

Transformation Name `New formula` `Single row formula` `STDEVIF(rain,city=='Center Town')` `city` `'stDevRainCT'`

You can use the following transforms to format the generated output. Note the `\$col` placeholder value for the multi-column transforms:

Transformation Name `Edit column with formula` `stDevRainCenterTown,maxWindSubZero` `numformat(\$col,'##.##')`

Since the following rely on data that has only one significant digit, you should format them differently:

Transformation Name `Edit column with formula` `varTempWRain,avgTempWRain,minTempWind5` `numformat(\$col,'##.#')`

Results:

datecityraintempwindavgTempWRainmaxWindSubZerominTempWind5varTempWRainstDevRainCT
1/23/17Valleyville0.0012.86.78.35.17.263.80.37
1/23/17Center Town0.319.45.37.3 532.60.37
1/23/17Magic Mountain0.000.07.31.66.43-3.9120.37
1/24/17Valleyville0.2517.23.38.35.17.263.80.37
1/24/17Center Town0.541.17.67.3 532.60.37
1/24/17Magic Mountain0.325.08.81.66.43-3.9120.37
1/25/17Valleyville0.023.36.88.35.17.263.80.37
1/25/17Center Town0.833.35.17.3 532.60.37
1/25/17Magic Mountain0.59-1.76.41.66.43-3.9120.37
1/26/17Valleyville1.0815.04.28.35.17.263.80.37
1/26/17Center Town0.966.17.67.3 532.60.37
1/26/17Magic Mountain0.77-3.93.01.66.43-3.9120.37
1/27/17Valleyville1.007.22.88.35.17.263.80.37
1/27/17Center Town1.3220.00.27.3 532.60.37
1/27/17Magic Mountain0.775.65.21.66.43-3.9120.37
1/28/17Valleyville0.12-6.15.18.35.17.263.80.37
1/28/17Center Town0.145.04.97.3 532.60.37
1/28/17Magic Mountain1.501.10.41.66.43-3.9120.37
1/29/17Valleyville0.3613.37.38.35.17.263.80.37
1/29/17Center Town0.756.19.07.3 532.60.37
1/29/17Magic Mountain0.603.36.01.66.43-3.9120.37

See Also for EXAMPLE - Conditional Calculations Functions:

• Page:
• Page:
• Page:
• Page:
• Page: