Metric Aggregation Functions
This applies to: Visual Data Discovery
Symphony provides a set of metric functions that are used to group (aggregate) data. The following aggregation methods can be selected for metrics in your visuals. See also Metrics.
| Aggregation Function | What Is Returned |
|---|---|
| AVG | The average of the data values for the field. This function is available only for numeric fields. |
| DISTINCT COUNT | The total number of unique values for the field. This function is available only for attribute and numeric fields. |
| COUNT | The total number of values for the field. This function is available only for attribute and numeric fields. |
| MIN | The lowest value in all the data values for the field. This function is available only for numeric fields. |
| MAX | The highest value in all the data values for the field. This function is available only for numeric fields. |
| SUM | The total of all the data values for the field. This function is available only for numeric fields. |
| LAST VALUE | The last value in all the data values for the field, sorted by the time attribute selected for the time bar. If the latest date and time for the time attribute is exactly the same in multiple records, the last value for the field is the maximum value of the field in the records with the latest date and time. See LAST VALUE Examples. This function is available only for numeric fields. |
| NO AGGREGATION | No Aggregation is available if you group and sort by the same field. When you use No Aggregation, you can select the sort Order of as Alphabetical (A-Z) or Reverse Alphabetical (Z-A). |
Suppose you have the following raw data:
| Name | Gender | Age |
|---|---|---|
| Johnny | Male | 10 |
| Adam | Male | 12 |
| Mina | Female | 11 |
| Jenny | Female | 13 |
| Ann | Female | 15 |
When this data is aggregated by gender, only two records (one for males and one for females) are returned and the aggregation must somehow determine what value to return for the age of the different genders. To do this, the aggregation requires input (using a metric function) about how the age should be returned. For example, if you elected to aggregate the data by gender and return the average age using the AVG metric function, the resulting data would be:
| Gender | Age Returned | Aggregation Calculation | Aggregation Logic |
|---|---|---|---|
| Male | 11 | 10 + 12 = 22 /2 = 11 | Johnny's and Adam's ages are summed and divided by 2 (two males). |
| Female | 13 | 11 + 13 + 15 = 39 /3 = 13 | Mina's, Jenny's, and Ann's ages are summed and divided by 3 (three females). |
If you elected to aggregate the data by gender and return the minimum age using the MIN metric function, the resulting data would be:
| Gender | Age Returned | Aggregation Logic |
|---|---|---|
| Male | 10 | Johnny's and Adam's ages are evaluated and the lower of the two ages is returned. |
| Female | 11 | Mina's, Jenny's, and Ann's ages are evaluated and the lower of the three ages is returned. |
LAST VALUE Examples
The LAST VALUE examples in this section use the following data:
| Record # | Gender | Country | Price | Items | Sale_Date |
|---|---|---|---|---|---|
| 1 | Male | US | 10 | 6 | 2019-01-03 |
| 2 | Male | US | 20 | 5 | 2019-01-02 |
| 3 | Male | UK | 30 | 4 | 2019-01-01 |
| 4 | Male | UK | 40 | 3 | 2019-01-01 |
| 5 | Male | UA | 50 | 2 | 2019-01-02 |
| 6 | Male | UA | 60 | 1 | 2019-01-03 |
| 7 | Female | US | 1 | 7 | 2019-01-04 |
| 8 | Female | US | 11 | 6 | 2019-01-03 |
| 9 | Female | US | 21 | 5 | 2019-01-02 |
| 10 | Female | UK | 31 | 4 | 2019-01-01 |
| 11 | Female | UK | 41 | 3 | 2019-01-01 |
| 12 | Female | UA | 51 | 2 | 2019-01-02 |
| 13 | Female | UA | 61 | 1 | 2019-01-03 |
| 14 | Female | UA | 71 | 0 | 2019-01-04 |
Examples: Grouping By One Field
Suppose you aggregate this data by Gender and request that the last value for Price be returned based on the Sale_Date. The results would be:
| Gender | Price Returned | Aggregation Logic |
|---|---|---|
| Male | 60 | In all the records for males, two records have the latest date (2019-01-03) - records #1 and #6. Therefore, the prices in both records are compared and the maximum price is returned. The result is 60 from record #6. |
| Female | 71 | In all the records for females, two records have the latest date (2019-01-04) - records #7 and #14. Therefore, the prices in both records are compared and the maximum price is returned. The result is 71 from record #14. |
Suppose you aggregate this data by Country and request that the last value for Price be returned based on the Sale_Date. The results would be:
| Country | Price Returned | Aggregation Logic |
|---|---|---|
| US | 1 | In all the records for the US, the record with the latest date is for the female with a sale date of 2019-01-04 (record #7). The price in that record is 1. |
| UK | 41 | All the records for the UK are for 2019-01-01. Therefore, the prices in all records are compared and the maximum price is returned. The result is 41 from record #11. |
| UA | 71 | In all the records for the UA, the record with the latest date is for a female with a sale date of 2019-01-04 (record #14). The price in that record is 71. |
Example: Grouping By Two Fields
Suppose you aggregate this data by Gender and then by Country and request that the last value for Price be returned based on the Sale_Date. The results would be:
| Gender | Country | Price Returned | Aggregation Logic |
|---|---|---|---|
| Male | US | 10 | The two records for US males are compared and the latest record has a sale date of 2019-01-03 (record #1). The price in that record is 10. |
| Male | UK | 40 | The two records for UK males have the same sale dates (2019-01-01). Therefore, the prices in all UK male records are compared and the maximum price is returned. The result is 40 from record #4. |
| Male | UA | 60 | The two records for UA males are compared and the latest record has a sale date of 2019-01-03 (record #6). The price in that record is 60. |
| Female | US | 1 | The three records for US females are compared and the latest record has a sale date of 2019-01-04 (record #1). The price in that record is 1. |
| Female | UK | 41 | The two records for UK females have the same sale dates (2019-01-01). Therefore, the prices in all UK female records are compared and the maximum price is returned. The result is 41 from record #11. |
| Female | UA | 71 | The three records for UA females are compared and the latest record has a sale date of 2019-01-03 (record #1). The price in that record is 10. |
Example: Grouping By Two LAST VALUE Metrics
Suppose you aggregate this data by Gender and request that the last value for Price and the last value for Items be returned based on the Sale_Date. The results would be:
| Gender | Price Returned | Items Returned | Aggregation Logic |
|---|---|---|---|
| Male | 60 | 6 | In all the records for males, two records have the latest date (2019-01-03) - records #1 and #6. The prices and item counts in both records are compared and the maximum price and item count are returned. The returned results are a price of 60 from record #6 and 6 items from record #1. |
| Female | 71 | 7 | In all the records for females, two records have the latest date (2019-01-04) - records #7 and #14. The prices and item counts in both records are compared and the maximum price and item count are returned. The returned results are a price of 71 from record #14 and 7 items from record #7. |