Pivot
The Pivot() function arranges and aggregates a range of information into a more readable format. This typically involves creating new columns using information from rows.
Related functions: Depivot, GroupBy
Syntax:
Qx.Pivot(Range, Row Grouping, Column Grouping, [Formula], [Calculate Error Value])- In Excel with Quintessence add-in versions prior to 25.1, use:
Pivot() - In the Quintessence Editor (Studio) using Quintessence language, use:
Pivot() - In the Quintessence Editor (Studio) using Python language, use:
Qx.Pivot()
ⓘ See parameter descriptions
| Range | The range of data to pivot. |
| Row Grouping | Columns on which row aggregation occurs. Creates a new row group for each unique set in the selected column(s). 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]. |
| Column Grouping | Columns on which column aggregation occurs. Creates a new column group for each unique set in the selected column(s). 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(C4) — 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 applied → no values.
- Calculate Error Value: Aggregation failures will produce empty results.
- An aggregate-type function is a utility function that performs aggregations on grouped data using Excel aggregate functions such as
AVERAGE,COUNT, andSUM.Pivot()is considered an aggregate-type function.
Examples:
The examples provided below are based on fictitious data for illustrative purposes only.
> Example 1: Pivot and sum a data range
Data block (A1:C12):
| Sector | Year | Number |
| Financial Services | 2013 | 400 |
| Basic Resources | 2013 | 250 |
| Healthcare | 2013 | 25 |
| Basic Resources | 2014 | 300 |
| Oil & Gas | 2013 | 350 |
| Basic Resources | 2014 | 35 |
| Insurance | 2014 | 800 |
| Financial Services | 2013 | 450 |
| Insurance | 2013 | 75 |
| Telecommunications | 2013 | 120 |
| Oil & Gas | 2013 | 450 |
- Row grouping: Sector
- Column grouping: Year
- Formula: Sum of Number
=Qx.Pivot(A2:C12, "C1", "C2", "sum(C3)")Result:
| 2013 | 2014 | |
| Basic Resources | 250 | 335 |
| Financial Services | 850 | |
| Healthcare | 25 | |
| Insurance | 75 | 800 |
| Oil & Gas | 800 | |
| Telecommunications | 120 |
> Example 2: Pivot and sum a data range using header notation
Data block (A1:C12):
| Sector | Year | Number |
| Financial Services | 2013 | 400 |
| Basic Resources | 2013 | 250 |
| Healthcare | 2013 | 25 |
| Basic Resources | 2014 | 300 |
| Oil & Gas | 2013 | 350 |
| Basic Resources | 2014 | 35 |
| Insurance | 2014 | 800 |
| Financial Services | 2013 | 450 |
| Insurance | 2013 | 75 |
| Telecommunications | 2013 | 120 |
| Oil & Gas | 2013 | 450 |
- Row grouping: Sector
- Column grouping: Year
- Formula: Sum of Number
=Qx.Pivot(A1:C12, "[Sector]", "[Year]", "sum([Number])")Result:
| 2013 | 2014 | |
| Basic Resources | 250 | 335 |
| Financial Services | 850 | |
| Healthcare | 25 | |
| Insurance | 75 | 800 |
| Oil & Gas | 800 | |
| Telecommunications | 120 |
> Example 3: Pivot and sum a data range over multiple columns
Data block (A1:E12):
| Asset | Sector | Year | Month | Number |
| JTD | Oil & Gas | 2000 | March | 620 |
| GPS | Industrials | 2000 | June | 250 |
| APN | Health Care | 2005 | June | 25 |
| CFH | Industrials | 2005 | March | 300 |
| PHO | Consumer Goods | 2000 | June | 800 |
| BVT | Industrials | 2005 | March | 35 |
| ZIK | Health Care | 2005 | March | 800 |
| JTD | Oil & Gas | 2000 | June | 450 |
| VRR | Consumer Goods | 2000 | June | 75 |
| PHO | Consumer Goods | 2000 | June | 480 |
| THT | Consumer Goods | 2000 | March | 250 |
- Row grouping: Asset, Sector
- Column grouping: Year, Month
- Formula: Sum of Number
=Qx.Pivot(A2:E12,"C1,C2","C3,C4","SUM(C5)")Result:
| 2000 | 2000 | 2005 | 2005 | ||
| June | March | June | March | ||
| APN | Health Care | 25 | |||
| BVT | Industrials | 35 | |||
| CFH | Industrials | 300 | |||
| GPS | Industrials | 250 | |||
| JTD | Oil & Gas | 450 | 620 | ||
| PHO | Consumer Goods | 1280 | |||
| THT | Consumer Goods | 250 | |||
| VRR | Consumer Goods | 75 | |||
| ZIK | Health Care | 800 |