Skip to main content

EXAMPLE - Conditional Calculations Functions

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