Pivot Mode

Introduction

A Pivot Table is a way to interactively summarize large amounts of data. Users can group data by specific rows or columns, or both! Then an aggregation formula is selected, such as sum or count, which is used to summarize data across the groups. 

Pivot Mode allows the ability to group data across Columns and Rows. When the Pivot Mode toggle is selected, a box labeled "Column Labels" appears, from which you can drag a single column into the box for grouping across the top by the values in that field.

How to get there

Login to Gigasheet > Select a Sheet > Right Hand Panel > Columns tab > toggle Pivot Mode

Pivot Table Considerations

To build a complete Pivot Table, apply each of the four main components:

1. How should filters be applied? Filter data by hitting the Filter button on the top nav, entering filter criteria, and hitting "Apply". Filters apply a restriction to the entire dataset, removing rows, and therefore modifying the aggregation calculations.
2. What makes up the Row Groupings? Row Groups are the equivalent of Groups in Gigasheet. You can specify the row groupings in 2 ways:
   
   1) Hit the Group button on the top nav, select a column, and hit "Apply"
   
   
     2) Drag the desired column name into the Row Groups box on the right panel
   
   
   When a field is chosen for the Row Group, the unique values of that field are listed for each row. Gigasheet allows for up to 1,000 row values in a Pivot Table. Gigasheet supports multiple levels of row groupings, so add more than one column to Rows to dig deep into your data.
   
   
3. How are the Values calculated? Aggregations are the calculations that will be applied to the column. Choose the calculation in two ways:
   
   1) Click on a cell in the column where you desire an aggregation and select the calculation. This will also make the column name appear in the Sum of Values Box
   
           2) Drag a column name to the Values box. Change the calculation by clicking on the column name, making a menu appear in the data section of the sheet.
   

   
   

   The most common calculations are sum, average, minimum and maximum.
   
   
4. What makes up the Column Groupings? To access Column Groupings, select the slider for Pivot Mode.
   
   
   Doing so will make the Columns box appear at the bottom:
   
   
   Column groupings can only be created by dragging a column into the Column Labels box. When a column is chosen, the unique values of the field are listed across the top as column headers. Gigasheet allows for up to 100 column values in a Pivot and only supports one column grouping at a time.

Example

In this example, we will be using generic sales data to explain how to use Pivot. We will try to answer the question how the average deal size selling to Big Box stores for each territory by quarter.

To enter Pivot mode, toggle the Pivot Mode selector in the right-side Column pane.

Choose the field values that should make up the rows by dragging the field into the "Row Groups" area. In this case, we will choose Territory. This will initially group the Territory field by unique value.

Next we will choose the columns. Again, drag the field of choice into the "Column Labels" area. In this example, we will choose "Quarter".

Next we need to choose what column to perform aggregations on. The sales figure is listed in the Amount field, so we will drag "Amount" into the Values area.

Now we're starting to get close. We see the values of "Territory" as the rows, the values of "Quarter" as the columns, and the sum of "Amount" indicating, for example, how many total dollars were involved in Miami transactions in 2020-Q3, and Houston 2020-Q2, and so on.

To change the aggregation, click the field in Values, and the various aggregation options appear. Available aggregations include count, empty, filled, unique, percent-empty, percent-filled, percent-unique, min, max, sum, avg, median, and range. We will switch to avg, giving us the average deal size per territory per quarter.

Finally, we can apply a filter. By narrowing our dataset down to only Big Box purchasers, we get the dataset we were seeking:

Moving between Grouped Data and Pivot Tables

If your data is already grouped and aggregated when entering Pivot Mode, you will only have to add a value to the Column Labels to make the data populate.

If you decide to leave Pivot Mode, Gigasheet will remove whatever column is in the Column Labels box and you will return to "Normal" mode with your Grouped and Aggregated rows.

Related Reading: Snowflake Pivot Tables