1. MFour Studio Education Center
  2. Resources
  3. Sandbox 201 - Proficient

Aggregate Function Index

Updated

ABOUT THIS DOCUMENT: 

An index of various aggregate functions including, Avg, Corr, Count, CountDistinct, CountIf, PercentileCont, PercentileDisc, Max, Median, Min, Nvd, StdDev, Sum, SumIf, SumProduct and

Variance.

Aggregate functions are special functions that summarize columns from lower levels. Aggregate functions can be used on on Grouped levels or in a Total column. For example, they can be used to Sum all of the values of a column, or Count all of the values for each grouping of a column. As such, aggregates must always refer to columns in a level lower than the result.

If the column being aggregated is grouped, then the aggregate is computed for each group. For example, if the worksheet is grouped by the column [Year], then placing Sum([Revenue]) on the [Year] level computes the revenue for each year.

The results of aggregate functions can be referenced by lower levels. An example would be to create a Sum([Amount]) column as Total and then an [Amount] / [Total] column as Percentage. This converts each Amount value into a relative percentage value.

Function Index:

Avg

Calculate the average of a column of numbers.

Formula

Avg(numbers)

  • numbers (required) The column of numbers that is being evaluated for the average.

Example

Avg([Population 2010] - [Population 2000])

  • Calculate the average change in population in a group.

Avg([Profit])

  • Calculate the average profit in a group.

 

Corr

Calculate the Pearson correlation coefficient, also known as the bivariate correlation, of two columns.

Formula

Corr(number column 1, number column 2)

  • number column 1 (required) A column of numbers representing the dependent or independent data.
  • number column 2 (required) A column of numbers representing the dependent or independent data.

 

Count

Count the non-Null values within a column or group.

Formula

=Count()

  • Field (optional) The column of values to be counted. Null values are skipped.

Example

Count()

  • Counts number of rows in a table.

Count([Funding Total Usd])

  • Count number of companies with a disclosed funding total.

 

CountDistinct

Counts the number of unique values within a column or group.

Formula

CountDistinct(field)

  • field (required) The column of values to be counted. Duplicate and Null values are skipped.

‍Example

CountDistinct([City])

  • Finds the number of unique city names

 

CountIf

Counts the number of rows in a table or group for which all given conditions are true.

Formula

CountIf(condition 1, ...)

  • condition 1 (required) The condition to test. If the condition is true, then the row will be counted. Note: you can use operators in conditions.
  • condition 2+ (optional) Additional conditions to test. If multiple conditions are given, they must all be True in order to be counted.

Example

CountIf([Age] > 65)

  • Count rows where Age is greater than 65

CountIf([Age] > 65, [Gender] = ”Female”)

  • Count rows where age is greater than 65 and Gender indicated is Female.

 

GrandTotal

Returns the grand total for the given aggregate formula. This function is shorthand for Subtotal(aggregate, "grand_total").

Syntax

GrandTotal(aggregate)

Parameters

Term Description
aggregate An aggregate formulaExamples: Count(), Sum([Sales Amount])

 

Example

GrandTotal(CountDistinct([Product Type]))

  • Calculates the grand total of the aggregate function.
  • The calculation is shown in the tooltip, but is usable anywhere an aggregate formula is allowed.

mceclip0.png

PercentileCont

Calculate the continuous k-th percentile value for a column or group.

Formula

PercentileCont(numbers, k)

  • numbers (required) The column of numbers to be searched.
  • k (required) Percentile fraction between 0 and 1.

NOTE: Interpolation is done between adjacent values if needed.

Example

PercentileCont([Score], 0.7)

  • Calculates the 70th percentile of scores. If the column values are 2, 4, 6, 8, and 10, the result would be 7.6.

PercentileDisc

Find the discrete k-th percentile value for a column or group.

Formula

PercentileDisc(number, k)

  • number (required) The column of numbers to be searched.
  • k (required) Percentile fraction between 0 and 1.

Example

PercentileDisc([Score], 0.7)

  • If the column values are 2, 4, 6, 8, and 10, the result would be 8.

 

PercentileOfTotal

Returns the percent of total for an aggregate formula.

Formula

PercentOfTotal(aggregate, [mode], [parameters])

Note: This function is shorthand for: aggregate / Subtotal(aggregate, mode, parameters).

 

Parameters

Term Description
aggregate An aggregate formula
Examples: Count(), Sum([Sales Amount])
mode(optional) The mode to use when calculating the aggregate formula. Mode determines which dimension(s) to use when performing the calculation.
Mode options vary between element types:
General purpose modes
●      "grand_total" (default for visualizations and pivot tables)— calculates the percent of grand total for the aggregate formula.
Visualization modes
●      "color" — calculates the aggregate percent of total using only the column specified for COLOR.●      "x_axis" — calculates the aggregate percent of total using only the column(s) placed on the X-AXIS.●      "trellis_column" / "trellis_row" — calculates the aggregate percent of total using only the dimension specified for the TRELLIS COLUMN or TRELLIS ROW.
Pivot table modes
●      "column" / "row" — calculates aggregate percent of total for the column or row total.●      "column_parent" / "row_parent" — calculates the aggregate percent of total for the column or row total, relative to the subtotal calculation for the parent dimensions.
Table modes
●      "grouping_parent" (default) — calculates the aggregate formula using the grouping keys from a parent grouping. This is equivalent to creating the aggregate calculation in a parent grouping and referencing it from a column in a lower grouping.
parameters(optional) An additional parameter specific to the mode.
This is only applicable to the following modes: "column_parent", "row_parent" and "grouping_parent". For these modes, this 3rd parameter specifies how many parent dimensions to ignore.

 

Examples

Example #1

  • PercentOfTotal(Sum([Quantity] * [Price]), "row")
    • The numerator of the percentage is the aggregate formula calculated along rows and columns.
    • The denominator of the percentage is the aggregate formula calculated along the rows.
    • The pivot table subtotals calculate the percent of total at the expected granularity.

mceclip1.png

Example #2

  • PercentOfTotal(Sum([Quantity] * [Price]), "x_axis")
    • The numerator of the percentage is the aggregate formula calculated along the x-axis and color.
    • The denominator of the percentage is the aggregate formula calculated along the x-axis.
    • The calculation is shown in the tooltip, but is usable anywhere an aggregate formula is allowed.

mceclip2.png

Max

Find the maximum value for a column or group.

Formula

Max(field)

  • field (required) The column to be searched. The function will search Numbers, Dates, and Strings, returning the maximum value.

NOTE: Sort order is affected by your database settings. For strings, sort order is most often blank spaces > special characters > numbers > uppercase letters > lowercase letters

For numbers, preceding and trailing zeroes most often have no effect on sort order.

Example

Max([Invoice Date])

  • Finds the greatest Invoice Date, which is the most recent invoice date.

Max([Name])

  • Finds the name that is the last alphabetically.

Median

Find the median of a column or group.

Formula

Median(numbers)

  • numbers (required) The column of numbers to be evaluated.

NOTE: If there is an even number of values, Median will interpolate between the middle values.

 

Example

Median([Temperature])

  • Find the median temperature value. If the column values were 3, 5, 7 and 20 the result would be 6.

Min

Find the minimum value for a column or group.

Formula

Min(field)

  • field (required) The column to be searched. The function will search Numbers, Dates, and Strings, returning the minimum value.

NOTE: Sort order is affected by your database settings. For strings, sort order is most often blank spaces > special characters > numbers > uppercase letters > lowercase letters

For numbers, preceding and trailing zeroes most often have no effect on sort order.

Example

Min([Balance])

  • Finds the smallest balance. Could be negative.

Min([Name])

  • Finds the first name alphabetically. If Name was “Betty”, “Charles” and “Anne”, the result would be”Anne”

Min([Invoice Date])

  • Finds the lowest Invoice Date, which is the invoice date furthest in the past.

 

Ndv

Counts the number of unique values within a column or group. This is an alias of the CountDistinct function and works the same way.

Formula

CountDistinct(Field)

  • Field (required) The column of values to be counted. Duplicate and Null values are skipped.

Example

CountDistinct([City])

  • Finds the number of unique city names

StdDev

Compute the standard deviation of a column or group.

Formula

StdDev(number)

  • number (required) The column of numbers to be measured.

Example

Stddev([Lap Time])

  • Find the standard deviation of lap times.

Subtotal

Returns the subtotal for an aggregate formula.

Formula

Subtotal(aggregate, mode, [parameters])

Parameters

Term Description
aggregate  An aggregate formula
Examples: Count(), Sum([Sales Amount])
mode The mode to use when calculating the aggregate formula. Mode determines which dimension(s) to use when performing the calculation.
Mode options vary between element types:
General purpose modes
●      "grand_total" — (default for visualizations and pivot tables)— calculates the subtotal for the aggregate formula.
Visualization modes
●      "color" — calculates the aggregate formula using only the column specified for COLOR.●      "x_axis" — calculates the aggregate formula using only the column(s) placed on the X-AXIS.●      "trellis_column" / "trellis_row" — calculates the aggregate subtotal using only the dimension specified for the TRELLIS COLUMN or TRELLIS ROW.
Pivot table modes
●      "column" / "row" — calculates aggregate subtotal for the column or row total.●      "column_parent" / "row_parent" — calculates the aggregate subtotal for the column or row total, relative to the subtotal calculation for the parent dimensions.
Table modes
●      "grouping_parent" — calculates the aggregate formula using the grouping keys from a parent grouping. This is equivalent to creating the aggregate calculation in a parent grouping and referencing it from a column in a lower grouping.
parameters(optional) An additional parameter specific to the mode.This is only applicable to the following modes: "column_parent", "row_parent" and "grouping_parent". For these modes, this 3rd parameter specifies how many parent dimensions to ignore.

 

Examples

Example #1

  • Subtotal(Avg([Price]), "column")
    • Calculates the aggregate subtotal for each column of the Product Type dimension.
    • Verify that the calculation values match the built-in pivot subtotals at the bottom of the table.

mceclip3.png

Example #2

  • Subtotal(CountDistinct([Product Type]), "x_axis")
    • Calculates the aggregate subtotal for each Product Type, across all values of Store Region.
    • The calculation is shown in the tooltip, but is usable anywhere an aggregate formula is allowed.

mceclip4.png

Sum

Sum the values in a column or group.

Formula

Sum(number)

  • number (required) Column of numbers to add together. Any Null values are skipped.

Example

Sum([Profit)]

  • Finds the total Profit.

SumIf

Sum the values in a column if all the conditions are true.

Formula

SumIf(number, condition 1, [condition 2], ...)

  • number (required) Column of numbers to add together. Null values are skipped.
  • condition 1 (required) Logical condition that returns a result that is either True or False. If the condition is True, the number on the corresponding row is added to the sum. Note: you can use operators in conditions.
  • condition 2 (optional) Additional conditions can be added after the first condition.

Note: In the case of multiple conditions the "AND" logical operator is used by default. In order to use an "OR" operator the conditions should be encapsulated in parenthesis chained by an explicit "OR".

Examples

SumIf( [Sales], [State] = "TX" )

  • Returns the sum of all the sales in Texas.

SumIf( [Sales], ([State] = "TX" OR [State] = "CA"))

  • Returns the sum of all the sales in Texas and California using the "OR" operator.

SumIf( [Sales], [State] = "TX", [CustomerID] = "1234" )

  • Returns the sum of all the sales in Texas for customer ID "1234".

SumProduct

Returns the sum of the product of values in a series of columns.

Formula

SumProduct(number, ...)

  • number (required): A column to be multiplied and summed. You can enter in any number of columns to be multiplied together.

Example

SumProduct([Quantity], [Unit Price])

  • Finds the sum of the product of the columns [Quantity]*[Unit Price]

Variance

Compute the statistical variance of a column or group.

Formula

Variance(number)

  • number (required) Column of numbers to calculate the variance of. Any Null values are skipped.

Example

Variance([Temperature])

  • Find the variance of temperature samples.
Was this article helpful?
1 out of 1 found this helpful

Comments

0 comments

Please sign in to leave a comment.

Articles in this section