Date: Thu, 6 Oct 2022 21:21:38 +0000 (UTC) Message-ID: <1113507499.8770.1665091298638@93e1396c9615> Subject: Exported From Confluence MIME-Version: 1.0 Content-Type: multipart/related; boundary="----=_Part_8769_1930892078.1665091298638" ------=_Part_8769_1930892078.1665091298638 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Content-Location: file:///C:/exported.html VAR Function

# VAR Function

Computes the variance among all values in a column. Input c= olumn can be of Integer or Decimal. If no numeric values are detected = in the input column, the function returns `0`

The variance of a set of values attempts to measur= e the spread in values around the mean. A variance of zero means that all v= alues are the same, and a small variance means that the values are closely = bunched together. A high value for variance indicates that the numbers are = spread out widely. Variance is always a positive value.

Var(X) =3D [Sum ((X - mean(X))2)] / Count(X)

If a row contains a missing or null value, it is not factored into the c= alculation.

Terms...
=20

Relevant terms:

=20
=20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20
TermDescription
PopulationPopulation statistical functions are computed fr= om all possible values. See https://en.wikip= edia.org/wiki/Statistical_population.
Sample=20
=20

Sample-based statistical functions are computed from a subset or sample = of all values. See https://en.wikipedia.org/w= iki/Sampling_(statistics).

=20

These function names include `SAMP` in their name.

=20
=20

NOTE: Statistical sampling has no relationship to the = samples taken within the product. When statistical functions are computed d= uring job execution, they are applied across the entire dataset. Sample met= hod calculations are computed at that time.

=20
=20
=20

The square root of variance is standard deviation, which is used to meas= ure variance under the assumption of a bell curve distribution. See STDEV Function.

For a version of this function co= mputed over a rolling window of rows, see ROLLINGVAR Function.

Wrangle vs. SQL: This function is part of Wrangle , a proprietary data transformation languag= e. Wrangle is not SQL. For more information,= see Wrangle Language. =

## Basic Usage

=20

=20

var(myRating)

Ou= tput: Returns the variance of the group of values from the&nb= sp;`myRating` column.

## Syntax = and Arguments

=20

=20

var(function_col_ref) [group:group_col_ref= ] [limit:limit_count]

=20 =20 =20 =20 =20
Argument Required? Data Type Description
function_col_ref Y string Name of column to which to apply the funct= ion

For more information on the `group` and ```limit= ``` parameters, see Pivot Transform.

For more information on syntax standards, see Language Documentation Syntax Note= s.

### function_col_ref

Name of the column the values of which you want to calculate the varianc= e. Column must contain Integer or Decimal values.

• Literal values are not supported as inputs.
• Multiple columns and wildcards are not supported.

Usage Notes:

=20

=20 =20 =20 =20
Required? Data Type Example Value
Yes String (column reference) `myValues`

## Examples=

=20
=20
=20

Tip: For additional examples, see Common Tasks.

=20

This example illustrates how you can apply statistical functions = to your dataset. Calculations include average (mean), max, min, standard de= viation, and variance.

Source:

Students took a test and recorded the following scores. You want to perf= orm some statistical analysis on them:

=20 =20 =20
Student Score
Anna 84
Ben 71
Caleb 76
Danielle 87
Evan 85
Faith 92
Gabe 85
Hannah 99
Ian 73
Jane 68

Transformation:

You can use the following transformations to calculate the average (mean= ), minimum, and maximum scores:

=20
=20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20
Transformation Name <= code>New formula `Single row formula` `AVERAGE(Score)` `'avgScore'`
=20

=20
=20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20
Transformation Name <= code>New formula `Single row formula` `MIN(Score)` `'minScore'`
=20

=20
=20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20
Transformation Name <= code>New formula `Single row formula` `MAX(Score)` `'maxScore'`
=20

To apply statistical functions to your data, you can use the ```VAR and STDEV functions, which can be used as the basis for = other statistical calculations.```

``` =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 Transformation Name<= code>New formulaParameter: Formula typeSingle row formulaParameter: FormulaVAR(Score)Parameter: New column namevar_Score =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 Transformation Name<= code>New formulaParameter: Formula typeSingle row formulaParameter: FormulaSTDEV(Score)Parameter: New column namestdev_Score =20 For each score, you can now calculate the variation of each one from the= average, using the following: =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 Transformation Name<= code>New formulaParameter: Formula typeSingle row formulaParameter: Formula((Score - avg_Score) / stdev_Score)= Parameter: New column name'stDevs' =20 Now, you want to apply grades based on a formula: =20 =20 =20 Grade standard deviations from avg (stDevs) A stDevs > 1 B stDevs > 0.5 C -1 <=3D stDevs <=3D 0.5 D stDevs < -1 F stDevs < -2 You can build the following transformation using the IF fun= ction to calculate grades. =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 = =20 =20 Transformation Name<= code>New formulaParameter: Formula typeSingle row formulaParameter: FormulaIF((stDevs > 1),'A',IF((stDevs < -2)= ,'F',IF((stDevs < -1),'D',IF((stDevs > 0.5),'B','C')))) =20 For more information, see IF Funct= ion. ```

```To clean up the content, you might want to apply some formatting to the = score columns. The following reformats the stdev_Score and stDevs``` columns to display two decimal places:

=20
=20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20
Transformation Name `Edit column with formula` `stdev_Score` `NUMFORMAT(stdev_Score, '##.00')`=20
=20

=20
=20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20
Transformation Name `Edit column with formula` `stDevs` `NUMFORMAT(stDevs, '##.00')`
=20

=20
=20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20
Transformation Name <= code>New formula `Single row formula` `MODE(Score)` `'modeScore'`
=20

Results:

=20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20
Student Score modeScore avgScore minScore maxScore var_Score stdev_Score stDevs Grade
Anna 84 85 82 68 99

87.00000000000001<= /p>

9.33 0.21 C
Ben 71 85 82 68 99 87.00000000000001 9.33 -1.18 D
Caleb 76 85 82 68 99 87.00000000000001 9.33 -0.64 C
Danielle 87 85 82 68 99 87.00000000000001 9.33 0.54 B
Evan 85 85 82 68 99 87.00000000000001 9.33 0.32 C
Faith 92 85 82 68 99 87.00000000000001 9.33 1.07 A
Gabe 85 85 82 68 99 87.00000000000001 9.33 0.32 C
Hannah 99 85 82 68 99 87.00000000000001 9.33 1.82 A
Ian 73 85 82 68 99 87.00000000000001 9.33 -0.96 C
Jane 68 85 82 68 99 87.00000000000001 9.33 -1.50 D
=20

=20
=20

=20

=20
• =20
Page:<= /span>=20
=20
• =20
• =20
Page:<= /span>=20
=20
• =20
• =20
Page:<= /span>=20
=20
• =20
• =20
Page:<= /span>=20
=20
• =20
• =20
Page:<= /span>=20
=20
• =20
• =20
Page:<= /span>=20
=20
• =20
• =20
Page:<= /span>=20
=20
• =20
• =20
Page:<= /span>=20
=20
• =20
• =20
Page:<= /span>=20
=20
• =20
• =20
Page:<= /span>=20
=20
• =20
=20

=20
=20
=20

------=_Part_8769_1930892078.1665091298638--