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). |
| Column Grouping | Columns on which column aggregation occurs. Creates a new column group for each unique set in the selected column(s). |
| 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). |
| 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 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 |