Aggregates.Add Method (ExPivot)

The Add method adds an Aggregate object to the Aggregates collection. An aggregate function processes the values of multiple rows, grouping them based on specific criteria to generate a single value that provides more meaningful insight or measurement, such as a set, bag, or list. Each Aggregate object is identified by a unique key.

By default, the Aggregates collection includes the following predefined aggregate functions:

sum (Summation). The sum function is the operation of adding a sequence of numbers. The result of this operation is the total or sum of all the numbers in the set. It is commonly used when you want to know the total of a numerical column or set of values. If you have the values 3, 5, 7, the sum function will add them together: sum = 3 + 5 + 7 = 15
min (Minimum). The min function finds the smallest value in a set of values. It compares all the numbers in the dataset and returns the least one. It is often used to find the lowest value in a range, such as the minimum temperature, price, or score. For the numeric values 12, 8, 19, 5, the min function will return: min = 5
max (Maximum). The max function, in contrast to the min function, finds the largest value in a set of numbers. It compares all the numbers and returns the greatest one, making it useful for identifying the highest value in a dataset. For the values 12, 8, 19, 5, the max function will return: max = 19
count (count) The count function counts the number of values in a dataset. It simply returns how many values are present in the set, regardless of their actual values. This function is useful for determining the size or number of elements in a collection. For the dataset 5, 8, 12, 5, the count function will return: count = 4
avg (Average) The avg function calculates the arithmetic mean of a set of values. It sums all the values and then divides that sum by the total number of values in the set. This gives you an overall "average" value of the dataset. For the values 3, 5, 7, the avg function will calculate: avg = (3 + 5 + 7) / 3 = 15 / 3 = 5
unique (Distinct Values) The unique function counts the distinct or unique values in a set, ignoring any duplicates. It’s useful when you need to determine how many unique items or values exist within a dataset. For the dataset 3, 3, 5, 7, 7, 7, the unique function will return: unique = 3 (because there are 3 distinct values: 3, 5, 7)

To customize the value or result of an aggregate function, use the FormatValue or FormatResult properties. For displaying aggregate functions on the columns, use the PivotColumns or PivotTotals properties.

The Aggregate objects are shown on the control's context menu as shown bellow:

For instance, here are a few equivalent expressions for predefined functions like:

sum (adding a sequence of numbers) => Add "sumEq", "dbl(pvalue) + dbl(value)", "sumEq", "sumEq"
count (counts the number of values in a dataset) => Add "countEq", "pvalue + 1", "countEq", "countEq"
avg (arithmetic mean of a set of values) => Add("avgEq", "dbl(pvalue) + dbl(value)", "avgEq", "avgEq").FormatResult = "value / vcount"
unique (counts the distinct or unique values in a set) => Add("uniqueEq", "pvalue + ((pvalue contains (';' + value)) ? `` : (';' + value))", "uniqueEq", "uniqueEq").FormatResult = "value count `;`"

Add "count-positive", "dbl(pvalue) + (dbl(value) > 0 ? 1 : 0)", "count-positive", "Count-positive", counts only positive values
Add "sum-negative", "dbl(pvalue) + (dbl(value) < 0 ? dbl(value) : 0)", "sum-negative", "Sum-negative", adds only negative values

		Type	Description
		Key as String	A String expression that specifies the unique key to identify the Aggregate object. The Key parameter should include only alpha-numeric characters. Any other characters are not included. The Key property specifies the Aggregate's key. An error is thrown if an attempt is made to add an aggregate with the same key that already exists in the collection.
		Base as Variant	The string expression defines either a base predefined aggregate function or an expression for a custom aggregate function. The predefined aggregate functions are: sum, adds all the values in the set to return the total min, returns the smallest value in the set max, returns the largest value in the set count, counts how many values are in the set avg, calculates the arithmetic mean by dividing the sum by the number of values unique, counts how many distinct values are in the set If a custom aggregate function is needed, the expression will define it. This expression supports the following keywords: value, to represent the current value being evaluated, pvalue, for the previously computed result of the expression, vcount, for the count of processed elements This expression supports predefined constants and operators/functions as described here. The result of the evaluated expression is stored in the next pvalue, which starts as empty. For example, to add the numbers 1, 2, and 3, consider this process: Initially, pvalue is empty, value is 1, and vcount is 1. The expression "pvalue + value" evaluates to 0 + 1 = 1, so pvalue becomes 1. On the next call, pvalue is 1, value is 2, and vcount is 2. The expression evaluates to 1 + 2 = 3, so pvalue becomes 3. Finally, when pvalue is 3, value is 3, and vcount is 3, the expression evaluates to 3 + 3 = 6. Any invalid expression will cause the Aggregate object to appear as disabled in the control's context menu.
		Name as Variant	A String expression that indicates the HTML caption to be shown on the control's context menu
		Caption as Variant	A String expression that indicates the HTML caption to be shown on the column's header.

		Return	Description
		Aggregate	An Aggregate object being created, that holds the newly object.