Pivot functions
Built-in functions for accessing values across pivot columns in your table calculations
Pivot functions let you work with values across pivot columns in your results table. When you pivot a dimension in Qyra, the values of that dimension become separate columns — pivot functions give you a way to reference and aggregate across those columns.
Pivot functions are only available when your query includes a pivoted dimension.
pivot_column
Returns the 0-based index of the current pivot column.
pivot_column()Parameters: None
Example
Use the column index to apply different logic per pivot column:
CASE WHEN pivot_column() = 0 THEN 'First' ELSE 'Other' ENDpivot_offset
Returns the value of an expression from a pivot column at a relative offset from the current column.
pivot_offset(expression, columnOffset)| Parameter | Type | Description |
|---|---|---|
expression | column reference or SQL expression | The expression to evaluate |
columnOffset | integer | Number of columns to offset. Negative = previous columns, positive = next columns, 0 = current column |
Returns NULL if the target column is not adjacent (e.g., if intermediate columns were filtered out).
Example
Compare the current pivot column's revenue against the previous pivot column:
${orders.total_revenue} - pivot_offset(${orders.total_revenue}, -1)pivot_index
Returns the value of an expression from a specific pivot column by its 0-based index.
pivot_index(expression, pivotIndex)| Parameter | Type | Description |
|---|---|---|
expression | column reference or SQL expression | The expression to evaluate |
pivotIndex | integer (≥ 0) | The 0-based pivot column index |
Example
Compare every pivot column's revenue against the first pivot column's revenue:
${orders.total_revenue} / pivot_index(${orders.total_revenue}, 0)pivot_where
Finds the first pivot column where a condition is true and returns a value from that column.
pivot_where(selectExpression, valueExpression)| Parameter | Type | Description |
|---|---|---|
selectExpression | SQL boolean expression | Condition to evaluate for each pivot column |
valueExpression | column reference or SQL expression | The expression to return from the matching column |
If multiple columns match, the value from the column with the lowest index is returned.
Example
Find the revenue from the first pivot column where the count exceeds 100:
pivot_where(${orders.count} > 100, ${orders.total_revenue})pivot_row
Returns an array of all values across the pivot columns for the current row.
pivot_row(expression)| Parameter | Type | Description |
|---|---|---|
expression | column reference or SQL expression | The expression to evaluate for each pivot column |
Example
Get all pivoted revenue values for the current row:
pivot_row(${orders.total_revenue})pivot_offset_list
Returns an array of values from consecutive pivot columns starting at a relative offset from the current column.
pivot_offset_list(expression, columnOffset, numValues)| Parameter | Type | Description |
|---|---|---|
expression | column reference or SQL expression | The expression to evaluate |
columnOffset | integer | Starting column offset. Negative = previous columns, positive = next columns, 0 = current column |
numValues | integer | Number of consecutive pivot columns to include |
Values are returned as NULL when the offset points to a non-adjacent pivot column (e.g., if columns were filtered out).
Example
Get the current and two previous pivot column values:
pivot_offset_list(${orders.total_revenue}, -2, 3)