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. |
| 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). |
| Calculate Error Value (optional) | The value that will be returned if the aggregation fails. |
Remarks:
- Unless changed by an administrator, 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: Use GroupBy() to calculate sum, average, and largest 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: 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 |