This example illustrates how to use the conditional calculation functions.
Functions:
| Item | Description |
|---|---|
| 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:
| date | city | rain | temp | wind |
|---|---|---|---|---|
| 1/23/17 | Valleyville | 0.00 | 12.8 | 6.7 |
| 1/23/17 | Center Town | 0.31 | 9.4 | 5.3 |
| 1/23/17 | Magic Mountain | 0.00 | 0.0 | 7.3 |
| 1/24/17 | Valleyville | 0.25 | 17.2 | 3.3 |
| 1/24/17 | Center Town | 0.54 | 1.1 | 7.6 |
| 1/24/17 | Magic Mountain | 0.32 | 5.0 | 8.8 |
| 1/25/17 | Valleyville | 0.02 | 3.3 | 6.8 |
| 1/25/17 | Center Town | 0.83 | 3.3 | 5.1 |
| 1/25/17 | Magic Mountain | 0.59 | -1.7 | 6.4 |
| 1/26/17 | Valleyville | 1.08 | 15.0 | 4.2 |
| 1/26/17 | Center Town | 0.96 | 6.1 | 7.6 |
| 1/26/17 | Magic Mountain | 0.77 | -3.9 | 3.0 |
| 1/27/17 | Valleyville | 1.00 | 7.2 | 2.8 |
| 1/27/17 | Center Town | 1.32 | 20.0 | 0.2 |
| 1/27/17 | Magic Mountain | 0.77 | 5.6 | 5.2 |
| 1/28/17 | Valleyville | 0.12 | -6.1 | 5.1 |
| 1/28/17 | Center Town | 0.14 | 5.0 | 4.9 |
| 1/28/17 | Magic Mountain | 1.50 | 1.1 | 0.4 |
| 1/29/17 | Valleyville | 0.36 | 13.3 | 7.3 |
| 1/29/17 | Center Town | 0.75 | 6.1 | 9.0 |
| 1/29/17 | Magic Mountain | 0.60 | 3.3 | 6.0 |
Transformation:
The following computes average temperature for rainy days by city:
| Transformation Name | New formula |
|---|---|
| Parameter: Formula type | Single row formula |
| Parameter: Formula | AVERAGEIF(temp, rain > 0) |
| Parameter: Group rows by | city |
| Parameter: New column name | 'avgTempWRain' |
The following computes maximum wind for sub-zero days by city:
| Transformation Name | New formula |
|---|---|
| Parameter: Formula type | Single row formula |
| Parameter: Formula | MAXIF(wind,temp < 0) |
| Parameter: Group rows by | city |
| Parameter: New column name | 'maxWindSubZero' |
This step calculates the minimum temp when the wind is less than 5 mph by city:
| Transformation Name | New formula |
|---|---|
| Parameter: Formula type | Single row formula |
| Parameter: Formula | MINIF(temp,wind<5) |
| Parameter: Group rows by | city |
| Parameter: New column name | 'minTempWind5' |
This step computes the variance in temperature for rainy days by city:
| Transformation Name | New formula |
|---|---|
| Parameter: Formula type | Single row formula |
| Parameter: Formula | VARIF(temp,rain >0) |
| Parameter: Group rows by | city |
| Parameter: New column name | 'varTempWRain' |
The following computes the standard deviation in rainfall for Center Town:
| Transformation Name | New formula |
|---|---|
| Parameter: Formula type | Single row formula |
| Parameter: Formula | STDEVIF(rain,city=='Center Town') |
| Parameter: Group rows by | city |
| Parameter: New column name | '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 |
|---|---|
| Parameter: Columns | stDevRainCenterTown,maxWindSubZero |
| Parameter: Formula | 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 |
|---|---|
| Parameter: Columns | varTempWRain,avgTempWRain,minTempWind5 |
| Parameter: Formula | numformat($col,'##.#') |
Results:
| date | city | rain | temp | wind | avgTempWRain | maxWindSubZero | minTempWind5 | varTempWRain | stDevRainCT |
|---|---|---|---|---|---|---|---|---|---|
| 1/23/17 | Valleyville | 0.00 | 12.8 | 6.7 | 8.3 | 5.1 | 7.2 | 63.8 | 0.37 |
| 1/23/17 | Center Town | 0.31 | 9.4 | 5.3 | 7.3 | 5 | 32.6 | 0.37 | |
| 1/23/17 | Magic Mountain | 0.00 | 0.0 | 7.3 | 1.6 | 6.43 | -3.9 | 12 | 0.37 |
| 1/24/17 | Valleyville | 0.25 | 17.2 | 3.3 | 8.3 | 5.1 | 7.2 | 63.8 | 0.37 |
| 1/24/17 | Center Town | 0.54 | 1.1 | 7.6 | 7.3 | 5 | 32.6 | 0.37 | |
| 1/24/17 | Magic Mountain | 0.32 | 5.0 | 8.8 | 1.6 | 6.43 | -3.9 | 12 | 0.37 |
| 1/25/17 | Valleyville | 0.02 | 3.3 | 6.8 | 8.3 | 5.1 | 7.2 | 63.8 | 0.37 |
| 1/25/17 | Center Town | 0.83 | 3.3 | 5.1 | 7.3 | 5 | 32.6 | 0.37 | |
| 1/25/17 | Magic Mountain | 0.59 | -1.7 | 6.4 | 1.6 | 6.43 | -3.9 | 12 | 0.37 |
| 1/26/17 | Valleyville | 1.08 | 15.0 | 4.2 | 8.3 | 5.1 | 7.2 | 63.8 | 0.37 |
| 1/26/17 | Center Town | 0.96 | 6.1 | 7.6 | 7.3 | 5 | 32.6 | 0.37 | |
| 1/26/17 | Magic Mountain | 0.77 | -3.9 | 3.0 | 1.6 | 6.43 | -3.9 | 12 | 0.37 |
| 1/27/17 | Valleyville | 1.00 | 7.2 | 2.8 | 8.3 | 5.1 | 7.2 | 63.8 | 0.37 |
| 1/27/17 | Center Town | 1.32 | 20.0 | 0.2 | 7.3 | 5 | 32.6 | 0.37 | |
| 1/27/17 | Magic Mountain | 0.77 | 5.6 | 5.2 | 1.6 | 6.43 | -3.9 | 12 | 0.37 |
| 1/28/17 | Valleyville | 0.12 | -6.1 | 5.1 | 8.3 | 5.1 | 7.2 | 63.8 | 0.37 |
| 1/28/17 | Center Town | 0.14 | 5.0 | 4.9 | 7.3 | 5 | 32.6 | 0.37 | |
| 1/28/17 | Magic Mountain | 1.50 | 1.1 | 0.4 | 1.6 | 6.43 | -3.9 | 12 | 0.37 |
| 1/29/17 | Valleyville | 0.36 | 13.3 | 7.3 | 8.3 | 5.1 | 7.2 | 63.8 | 0.37 |
| 1/29/17 | Center Town | 0.75 | 6.1 | 9.0 | 7.3 | 5 | 32.6 | 0.37 | |
| 1/29/17 | Magic Mountain | 0.60 | 3.3 | 6.0 | 1.6 | 6.43 | -3.9 | 12 | 0.37 |
This page has no comments.