GroupBy
The GroupBy() function groups a range of data into sets and calculates values for each set using aggregation functions such as SUM, AVERAGE, and COUNT.
Related functions: Pivot
Syntax:
Qx.GroupBy(Range, Group Columns, [Formulas], [Calculate Error Value])- In Excel with Quintessence addin versions prior to 25.1, use:
GroupBy() - In the Quintessence Editor (Studio) using Quintessence language, use:
GroupBy() - In the Quintessence Editor (Studio) using Python language, use:
Qx.GroupBy()
ⓘ See parameter descriptions
| Range | The range to manipulate. |
| Group Columns | The columns that the GroupBy() function will group by, in the specified order.Columns are referenced as C1, C2, C3, etc.If the first row contains headers, header notation may also be used. For example, if a column header is Price, reference it as [Price]. |
| Formulas (optional) | The aggregation functions used to calculate the grouped output, for example: SUM(V4) — where 4 refers to the column number (i.e., sum the values in column 4 for each grouped set). Columns are referenced as C1, C2, C3, etc.If the first row contains headers, header notation may also be used. For example, if a column header is Price, reference it as [Price]. |
| Calculate Error Value (optional) | The value that will be returned if the aggregation fails. |
Remarks:
- The default parameters are:
- Formulas: No formula will be passed. In this case,
GroupBy()behaves like aUnique()function applied to the Group Columns parameter.
- Calculate Error Value: Aggregation failures will produce empty results.
- Formulas: No formula will be passed. In this case,
- An aggregate-type function is a utility function that performs aggregations on grouped data using Excel aggregate functions such as
AVERAGE,COUNT, andSUM.GroupBy()is considered an aggregate-type function.
Examples:
The examples provided below are based on fictitious data for illustrative purposes only.
> Example 1: Calculate sum, average, and max for each group
The following source data is in cell range A1:D13:
| Fund | Asset | Year | % Return |
| AGRI PROVIDENT FUND | MBD | 2013 | 10 |
| AGRI PROVIDENT FUND | KVJ | 2013 | 20 |
| AGRI PROVIDENT FUND | GMP | 2013 | 17 |
| NNN LIFE LIMITED | YKG | 2013 | 45 |
| NNN LIFE LIMITED | PHO | 2013 | 12 |
| NNN LIFE LIMITED | BSF | 2013 | 14 |
| AGRI PROVIDENT FUND | MBD | 2014 | 12 |
| AGRI PROVIDENT FUND | KVJ | 2014 | 15 |
| AGRI PROVIDENT FUND | GMP | 2014 | 25 |
| NNN LIFE LIMITED | YKG | 2014 | 32 |
| NNN LIFE LIMITED | PHO | 2014 | 13 |
| NNN LIFE LIMITED | BSF | 2014 | 11 |
We want to find the sum, average, and largest value for each fund in each year. This can be achieved using the following formula:
=Qx.GroupBy(A2:D13,"C1,C3","SUM(V4),AVERAGE(V4),LARGE(V4,1)")Result:
| AGRI PROVIDENT FUND | 2013 | 47 | 15.66667 | 20 |
| AGRI PROVIDENT FUND | 2014 | 52 | 17.33333 | 25 |
| NNN LIFE LIMITED | 2013 | 71 | 23.66667 | 45 |
| NNN LIFE LIMITED | 2014 | 56 | 18.66667 | 32 |
> Example 2: Calculate sum, average, and max for each group using header notation
The following source data is in cell range A1:D13:
| Fund | Asset | Year | % Return |
| AGRI PROVIDENT FUND | MBD | 2013 | 10 |
| AGRI PROVIDENT FUND | KVJ | 2013 | 20 |
| AGRI PROVIDENT FUND | GMP | 2013 | 17 |
| NNN LIFE LIMITED | YKG | 2013 | 45 |
| NNN LIFE LIMITED | PHO | 2013 | 12 |
| NNN LIFE LIMITED | BSF | 2013 | 14 |
| AGRI PROVIDENT FUND | MBD | 2014 | 12 |
| AGRI PROVIDENT FUND | KVJ | 2014 | 15 |
| AGRI PROVIDENT FUND | GMP | 2014 | 25 |
| NNN LIFE LIMITED | YKG | 2014 | 32 |
| NNN LIFE LIMITED | PHO | 2014 | 13 |
| NNN LIFE LIMITED | BSF | 2014 | 11 |
We want to find the sum, average, and largest value for each fund in each year. This can be achieved using the following formula:
=Qx.GroupBy(A1:D13, "[Fund], [Year]","SUM([% Return]) as [Sum], AVERAGE([% Return]) as [Average], LARGE([% Return],1) as [Largest]")Result:
| Fund | Year | Sum | Average | Largest |
| AGRI PROVIDENT FUND | 2013 | 47 | 15.66667 | 20 |
| AGRI PROVIDENT FUND | 2014 | 52 | 17.33333 | 25 |
| NNN LIFE LIMITED | 2013 | 71 | 23.66667 | 45 |
| NNN LIFE LIMITED | 2014 | 56 | 18.66667 | 32 |
> Example 3: Group and Sum at multiple levels – by Sector and by Asset within a Sector
The following source data is in cell range A1:D11:
| Sector | Fund No | Asset | Value |
| Basic Materials | 5000 | KVJ | 5250 |
| Consumer Goods | 5010 | XDU | 9822 |
| Basic Materials | 5020 | CEB | 1167 |
| Consumer Goods | 5030 | XDU | 10311 |
| Consumer Goods | 5040 | XDU | 410 |
| Industrials | 5050 | ZQE | 1350 |
| Consumer Goods | 5060 | NHP | 59 |
| Consumer Goods | 5070 | QGW | 180 |
| Consumer Goods | 5080 | QJU | 40 |
| Industrials | 5090 | UAG | 15 |
We need to calculate:
- The total value of each sector and asset (grouping level 2 –
G2) - The total value of each sector (grouping level 1 –
G1)
This can be achieved using the following formula:
=Qx.GroupBy(A2:D11,"C1,C3","SUM(G2V4),SUM(G1V4)")Result:
| Basic Materials | CEB | 1167 | 6417 |
| Basic Materials | KVJ | 5250 | 6417 |
| Consumer Goods | NHP | 59 | 20822 |
| Consumer Goods | QGW | 180 | 20822 |
| Consumer Goods | QJU | 40 | 20822 |
| Consumer Goods | XDU | 20543 | 20822 |
| Industrials | UAG | 16 | 1366 |
| Industrials | ZQE | 1350 | 1366 |