Aggregate Functions Flashcards
ATTR
Returns the value of the expression if it has a single value for all rows. Otherwise returns an asterisk. Null values are ignored.
AVG
Returns the average of all the values in the expression. AVG can be used with numeric fields only. Null values are ignored.
COLLECT
An aggregate calculation that combines the values in the argument field. Null values are ignored.
Note: The COLLECT function can only be used with spatial fields.
Example:
COLLECT ([Geometry])
CORR
Returns the Pearson correlation coefficient of two expressions.
The Pearson correlation measures the linear relationship between two variables. Results range from -1 to +1 inclusive, where 1 denotes an exact positive linear relationship, as when a positive change in one variable implies a positive change of corresponding magnitude in the other, 0 denotes no linear relationship between the variance, and −1 is an exact negative relationship.
Note: The square of a CORR result is equivalent to the R-Squared value for a linear trend line model. See Trend Line Model Terms.
Example:
You can use CORR to visualize correlation in a disaggregated scatter plot. The way to do this is to use a table-scoped level of detail expression. For example:
{CORR(Sales, Profit)}
With a level of detail expression, the correlation is run over all rows. If you used a formula like CORR(Sales, Profit) (without the surrounding brackets to make it a level of detail expression), the view would show the correlation of each individual point in the scatter plot with each other point, which is undefined.
See Table-Scoped
COUNT
Returns the number of items in a group. Null values are not counted.
COUNTD
Returns the number of distinct items in a group. Null values are not counted. This function is not available in the following cases: workbooks created before Tableau Desktop 8.2 that use Microsoft Excel or text file data sources, workbooks that use the legacy connection, and workbooks that use Microsoft Access data sources. Extract your data into an extract file to use this function. See Extract Your Data.
COVAR
Returns the sample covariance of two expressions.
Covariance quantifies how two variables change together. A positive covariance indicates that the variables tend to move in the same direction, as when larger values of one variable tend to correspond to larger values of the other variable, on average. Sample covariance uses the number of non-null data points n - 1 to normalize the covariance calculation, rather than n, which is used by the population covariance (available with the COVARP function). Sample covariance is the appropriate choice when the data is a random sample that is being used to estimate the covariance for a larger population.
COVAR is available with the following data sources:
Tableau data extracts (you can create an extract from any data source) Cloudera Hive EXASolution Firebird (version 3.0 and later) Google BigQuery Hortonworks Hadoop Hive IBM PDA (Netezza) Oracle PostgreSQL Presto SybaseIQ Teradata Vertica For other data sources, consider either extracting the data or using WINDOW_COVAR. See Table Calculation Functions.
If expression1 and expression2 are the same—for example, COVAR([profit], [profit])—COVAR returns a value that indicates how widely values are distributed.
Note: The value of COVAR(X, X) is equivalent to the value of VAR(X) and also to the value of STDEV(X)^2.
Example:
The following formula returns the sample covariance of Sales and Profit.
COVAR([Sales], [Profit])
COVARP
Returns the population covariance of two expressions.
Covariance quantifies how two variables change together. A positive covariance indicates that the variables tend to move in the same direction, as when larger values of one variable tend to correspond to larger values of the other variable, on average. Population covariance is sample covariance multiplied by (n-1)/n, where n is the total number of non-null data points. Population covariance is the appropriate choice when there is data available for all items of interest as opposed to when there is only a random subset of items, in which case sample covariance (with the COVAR function) is appropriate.
If expression1 and expression2 are the same—for example, COVARP([profit], [profit])—COVARP returns a value that indicates how widely values are distributed.
Note: The value of COVARP(X, X) is equivalent to the value of VARP(X) and also to the value of STDEVP(X)^2.
Example:
The following formula returns the population covariance of Sales and Profit.
COVARP([Sales], [Profit])
MEDIAN
Returns the median of an expression across all records. Median can only be used with numeric fields. Null values are ignored. This function is not available for workbooks created before Tableau Desktop 8.2 or that use legacy connections. It is also not available for connections using any of the following data sources.
MIN
Returns the minimum of an expression across all records. If the expression is a string value, this function returns the first value where first is defined by alphabetical order.
MAX
Returns the maximum of an expression across all records. If the expression is a string value, this function returns the last value where last is defined by alphabetical order.
PERCENTILE
Returns the percentile value from the given expression corresponding to the specified number. The number must be between 0 and 1 (inclusive)—for example, 0.66, and must be a numeric constant.
STDEV
Returns the statistical standard deviation of all values in the given expression based on a sample of the population.
STDEVP
Returns the statistical standard deviation of all values in the given expression based on a biased population.
SUM
Returns the statistical standard deviation of all values in the given expression based on a biased population.
