Window Aggregation Functions
This applies to: Visual Data Discovery
Window aggregation functions are a middle case between column and table aggregation functions. They provide a snapshot or window into a subset of data, depending on the groupings used by the visual. Each window function such as WindowSUM or WindowAVG requires a numeric field to aggregate followed by a list of one or more attributes. The function aggregates the data and groups the results based on these attributes if the attributes are present in the visual. Attributes absent from the visual are ignored from the aggregation.
Derived fields can be used in window aggregation functions.
For example, an aggregation WindowAVG( profits, gender, city ) returns the average profits in the data, grouped by gender and city if gender and city are represented in the visual. If gender happens to be absent from the visual, then it is dropped from the aggregation. Effectively, the average profits would then be grouped only by city.
The following table describes the supported window aggregation functions.
| Function | Type | Description |
|---|---|---|
WindowAVG(<field>,<attr1>[,<attr2>]...) |
numeric |
Returns the average of a column (field), grouped by the specified attributes. |
WindowCOUNT(<field>,<attr1>[,<attr2>]... ) |
any |
Returns the numeric count of values in a column (field), grouped by the specified attributes. 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 |
WindowCOUNTD(<field>,<attr1>[,<attr2>]...) |
any |
Returns the numeric count of unique values in a column (field), grouped by the specified attributes. 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 |
WindowMAX(<field>,<attr1>[,<attr2>]...) |
numeric |
Returns the maximum value of a column (field), grouped by the specified attributes. |
WindowMIN(<field>,<attr1>[,<attr2>]...) |
numeric |
Returns the minimum value of a column (field), grouped by the specified attributes. |
WindowSUM(<field>,<attr1>[,<attr2>]...) |
numeric |
Returns the sum of a column (field), grouped by the specified attributes. |
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 |
To create a custom metric containing a group's contribution to just gender, rather than to the whole, use the following formula.
SUM(earned)/WindowSUM(earned,gender) * 100
Using this custom metric in a pivot table with the example data set shown above produces results similar to the ones shown below.
| City | Gender | Volume | % of Each Gender's Earnings |
|---|---|---|---|
| Reston | F | 1 | 44.44 |
| M | 1 | 10 | |
| Rockville | F | 1 | 55.56 |
| M | 2 | 90 | |
| Total | 5 | 100 |
In this pivot table, each city's total earnings (SUM(earned)) is shown as a percentage of each gender's total earnings. If gender had been absent from the visual, the cities' total earnings would have been shown as totals of the whole, rather than of each gender.