Pivot Tables

A pivot table aggregates and transposes column values in the dataflow to make it easier to analyze data visually. With pivot, you can arrange input columns into a cross tabulation format (also known as crosstab) that produces rows, columns and summarized values. You can also use fields as input and not display them. You can use pivot to pivot on two dimensions or to group aggregate data on one dimension.

This example shows sales data for shirts.

Table 1. Input Data
Region Gender Style Ship Date Units Price Cost
East Boy Tee 1/31/2018 12 11.04 10.42
East Boy Golf 6/31/2018 12 13.00 10.60
East Boy Fancy 2/25/2018 12 11.96 11.74
East Girl Tee 1/31/2018 10 11.27 10.56
East Girl Golf 6/31/2018 10 12.12 11.95
East Girl Fancy 1/31/2018 10 13.74 13.33
West Boy Tee 1/31/2018 11 11.44 10.94
West Boy Golf 2/25/2018 11 12.63 11.73
West Boy Fancy 2/25/2018 11 12.06 10.51
West Girl Tee 2/25/2018 15 13.42 13.29
West Girl Golf 6/31/2018 15 11.48 10.67
North Boy Tee 2/25/2018 17 16.04 10.42
North Boy Fancy 2/25/2018 12 11.56 12.42
North Girl Tee 2/25/2018 16 12.32 18.42
North Boy Golf 1/31/2018 18 11.78 13.23
North Girl Tee 2/25/2018 12 18.45 11.64
North Girl Golf 2/25/2018 14 11.23 19.85
North Boy Fancy 1/31/2018 16 12.54 13.42
North Girl Tee 2/25/2018 17 181.73 15.83
South Boy Fancy 1/31/2018 19 14.15 13.42
South Girl Tee 2/25/2018 11 11.85 12.92
South Girl Fancy 1/31/2018 13 11.54 14.35
South Boy Tee 2/25/2018 15 14.14 14.73
South Boy Golf 2/25/2018 16 17.83 17.83
South Girl Fancy 6/31/2018 11 18.24 12.35
South Girl Tee 1/31/2018 20 19.94 12.95
South Boy Golf 2/25/2018 12 21.25 19.56

We want to be able to determine how many units we sold in each region for every ship date. To do this, we use pivot to generate this table:

Table 2. Pivot Table
Region 1/31/2018_ShipDate 2/25/2018_ShipDate 6/31/2018_ShipDate
East 32 12 22
North 34 88  
South 52 54 11
West 11 37 15

In this case, the column is Ship Date, the row is Region, and the data we would like to see is Units. The total number of units shipped is displayed here using a sum aggregation.