Value Aggregation functions are used to perform calculations on a list of values.
Syntax
AggregationType( <Value List> )
Parameters
<Value List> : A Dimension or an arithmetic expressions made up of Numeric Dimensions and/or numbers.
Description
Iterates over all values in <Value List> and:
| Aggregation Type | Description |
| Sum | Returns total of all values |
| Avg (Average) | Returns the average value |
| Min (Minimum) | Returns the minimal value |
| Max (Maximum) | Returns the maximal value |
| Count | Returns the number of unique values |
| CountDup | Returns the number of values |
Note
Measures are in fact pre-packaged Value Aggregation Functions.
Example 1
Consider the following raw data:
| Product | Store | Sales |
| Ice Cream | Store A | 100 |
| Ice Cream | Store B | 200 |
| Bread | Store A | 150 |
| Milk | Store B | 400 |
The following tables will describe how each of the functions work. The functions are marked in color and Dimensions are marked in bold brackets [ ].
| [Product] | Sum([Sales]) | Avg([Sales]) | Min([Sales]) | Max([Sales]) |
| Ice Cream | 300 {100+200} | 150 {(100+200)/2} | 100 | 200 |
| Bread | 150 | 150 | 150 | 150 |
| Milk | 400 | 300 | 400 | 400 |
| [Store] | Sum([Sales]) | Avg([Sales]) | Min([Sales]) | Max([Sales]) |
| Store A | 250 {100+150} | 125 {(100+150)/2} | 100 | 150 |
| Store B | 600 {200+400} | 300 {(200+400)/2} | 200 | 400 |
| [Product] | Count([Store]) |
| Ice Cream | 2 {Store A and Store B} |
| Bread | 1 {Store A} |
| Milk | 1 {Store B} |
| [Product] | CountDup([Product]) |
| Ice Cream | 2 {'Ice Cream' appears twice} |
| Bread | 1 {'Bread' appears once} |
| Milk | 1 {'Milk' appears once} |
Example 2
Consider the following transactional sales data:
| Transaction Time | Product | Price Per Unit | Quantity Sold |
| 12:00 | Shoes | 10 | 2 |
| 13:00 | Shoes | 10 | 4 |
| 14:00 | Shirt | 5 | 3 |
| 15:00 | Shirt | 5 | 6 |
Sales is calculated by multiplying Price Per Unit with Quantity Sold. However, if you multipled the 'Sum Per Per Unit' Measure with the 'Sum Quantity Sold' Measure, you would get incorrect results.
| [Product] | Sum([Price Per Unit]) | Sum([Quantity Sold]) | Sum([Price Per Unit]) * Sum([Quantity Sold]) |
| Shoes | 20 {10+10} | 6 {2+4} | 120 {20*6} |
| Shirt | 10 {5+5} | 9 {3+6} | 90 {10*9} |
According the the calculation above, shoes were sold for 120 dollars. This is incorrect because the price of a pair of shoes is 10 dollars, not 20. Total sales for shoes should have been 6 * 10 dollars = 60 dollars. The wrong value is because the price is aggregated before multiplication with the quantity is done.
To get correct results, multiply the 'Price Per Unit' Dimension and 'Quantity Sold' Dimension values first and only then perform the aggregation:
Sum([Price Per Unit] * [Quantity Sold])