pivot_index function

The pivot_index function can be used in table calculations to extract and manipulate the value of a pivoted column based on its index (in other words, its position).

Syntax

pivot_index(expression, pivot_index)

The pivot_index function evaluates expression in the context of the pivot column at the position pivot_index (1 for first pivot, 2 second pivot, etc.) The function returns null for unpivoted results.

Examples

The following table shows a query that counts the number of orders made in each season of the year. The query includes a pivoted dimension, Products Category, which has the values Swimwear and Coats. To compute the total number of orders made in each season across all categories, create a Total Orders Count table calculation.

Products Category Swimwear Coats Total Orders Count
Season Orders Count Orders Count
Winter 3 671 674
Spring 278 120 398
Summer 840 21 861
Fall 30 432 462

The Total Orders Count table calculation adds the value of the first pivot column to the value of the second pivot column. The formula is:

pivot_index(${orders.count}, 1) + pivot_index(${orders.count}, 2)

Things to know

pivot_index cannot be used in a custom filter.