Table Aggregation Functions
This applies to: Visual Data Discovery
Table aggregation functions are broader in scope than column aggregation functions. Table aggregation functions use all data from a field and produce a single, ungrouped value. You typically do not use the result directly in a visual, since it is ungrouped. For example, if you have sales records grouped by gender, a TableSUM
custom metric returns the total sales of all records as one value, regardless of the group in consideration. The TableSUM
result would include both the male and female data values. Consequently, males and females would appear to have the same sales if the TableSUM
result was included in the visual. Table aggregation functions are typically used to calculate percentages of a whole or average values.
The following table describes the supported table aggregation functions.
Function | Type | Description |
---|---|---|
| numeric | Returns the average of a column (field), regardless of how the visual is grouped. |
| any | Returns the numeric count of values in a column (field), regardless of how the visual is grouped. This aggregate function normally ignores null values for the specified field. Consequently, the result of this aggregate function may not be the same as the actual number of records in the data. Use the wildcard character (*) for |
| any | Returns the numeric count of unique values in a column (field), regardless of how the visual is grouped. This aggregate function normally ignores null values for the specified field. Consequently, the result of this aggregate function may not be the same as the actual number of records in the data. Use the wildcard character (*) for |
TableMAX(<field>) | numeric | Returns the maximum value of a column (field), regardless of how the visual is grouped. |
TableMIN(<field>) | numeric | TableMIN returns the minimum value of a column (field), regardless of how the visual is grouped. |
| numeric | TableSUM returns the sum of a column (field), regardless of how the visual is grouped. |
Example
Suppose you have the following fields and data:
name | gender | city | earned | spent |
---|---|---|---|---|
Alan | M | Rockville | $10 | $2 |
Bob | M | Rockville | $8 | $3 |
Carol | F | Rockville | $5 | $5 |
Darlene | F | Reston | $4 | $6 |
Ed | M | Reston | $2 | $8 |
Using this data, you can create a custom metric containing individual earnings as a percentage of total earnings with the following formula:
SUM(earned) / TableSUM(earned) * 100
in which:
SUM(earned)
calculates the sum earnings for each individual.TableSUM(earned)
calculates the total earnings of all records in the data.- The quotient of
SUM(earned) / TableSUM(earned)
is multiplied by 100 to convert the result into a percentage.
Shown on a table using the example data above, the results would look like this:
Name | % of Whole |
---|---|
Alan | 34.48 |
Bob | 27.59 |
Carol | 17.24 |
Darlene | 13.79 |
Ed | 6.90 |
Total | 100 |
Comments
0 comments
Please sign in to leave a comment.