Date: Fri, 24 Sep 2021 22:22:06 +0000 (GMT) Message-ID: <1801094734.9299.1632522126988@9c5033e110b2> Subject: Exported From Confluence MIME-Version: 1.0 Content-Type: multipart/related; boundary="----=_Part_9298_1085876375.1632522126988" ------=_Part_9298_1085876375.1632522126988 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 language.= Wrangle is not SQL. For more information, se= e Wrangle Language.

## Basic Usage

=20

var(myRating)

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

## Syntax a= nd Arguments

=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
Required? Data Type Example Value
Yes String (column reference) `myValues`

## Examples<= /h2> Tip: For additional examples, see Common Tasks. 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 formulaParameter: Formula type`Single row formula`Parameter: Formula`AVERAGE(Score)`Parameter: New column name`'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 formulaParameter: Formula type`Single row formula`Parameter: Formula`MIN(Score)`Parameter: New column name`'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 formulaParameter: Formula type`Single row formula`Parameter: Formula`MAX(Score)`Parameter: New column name`'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`Parameter: Columns`stdev_Score`Parameter: Formula`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`Parameter: Columns`stDevs`Parameter: Formula`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 formulaParameter: Formula type`Single row formula`Parameter: Formula`MODE(Score)`Parameter: New column name`'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 See Also:=20 =20 =20 =20 Page:= =20 =20 =20 KTHL= ARGESTUNIQUEIF Function=20 =20 =20 =20 Page:= =20 =20 =20 KTHLARGEST F= unction=20 =20 =20 =20 Page:= =20 =20 =20 LIST Function= =20 =20 =20 =20 Page:= =20 =20 =20 KTHLAR= GESTUNIQUE Function=20 =20 =20 =20 Page:= =20 =20 =20 VARSAMP Functio= n=20 =20 =20 =20 Page:= =20 =20 =20 UNIQUE Function<= /a>=20 =20 =20 =20 Page:= =20 =20 =20 VAR Function=20 =20 =20 =20 Page:= =20 =20 =20 KTHLAR= GESTDATEIF Function=20 =20 =20 =20 Page:= =20 =20 =20 KT= HLARGESTUNIQUEDATE Function=20 =20 =20 =20 Page:= =20 =20 =20 = KTHLARGESTUNIQUEDATEIF Function=20 =20 =20 =20 Page:= =20 =20 =20 COUNTA Function<= /a>=20 =20 =20 =20 Page:= =20 =20 =20 A= PPROXIMATEPERCENTILE Function=20 =20 =20 =20 Page:= =20 =20 =20 CORREL Function<= /a>=20 =20 =20 =20 Page:= =20 =20 =20 PERCENTILE F= unction=20 =20 =20 =20 Page:= =20 =20 =20 QUARTILE Funct= ion=20 =20 =20 =20 =20

------=_Part_9298_1085876375.1632522126988--