A pivot table is a versatile tool that helps you summarize the information in a table by slicing and dicing it so you can look at it from different perspectives. It works by grouping records together based on either one or two fields, and summarizing the data from those records, either by counting the number of records per group, or by using a specific summary function (like sum, average, or maximum) on another field.
Watch this video to learn more about how to set up and use a pivot table block, or read on for further information.
Why use a pivot table?
A pivot table is a versatile tool that can be used to summarize the information in a table in new and useful ways by creating another table. It's useful for when you have lots of numbers that need summarizing, when you want to compare subsets of data, or when you’re trying to find trends in your data.
Setting up a pivot table
To get started, first install the pivot table block. Once installed, the block will open up to fill your screen. By default, the block will automatically select the most recent table and view in which you were working, but you can change the target table and view as desired.
Row grouping allows you to select the field which will define the rows of your pivot table, whereas column grouping allows you to select the field which will define the columns of your pivot table.
Note that while a row grouping is required to build a pivot table, a column grouping is not required. If you select None as the column grouping option, then there will be no column grouping, and the summary function will just apply to the row groups. (This looks somewhat similar to using grouped records and the summary bar to create reports.)
When picking your row and column groupings, if you have a field where each cell can contain multiple choices (such as a collaborator field allowing multiple collaborators, a linked record field allowing linking to multiple records, or a multiple select field), you will have the option to split multiple choices.
Splitting multiple choices in a row or column grouping means that a record containing multiple choices in the specified field will be counted/summarized separately for each choice. For example, a record containing options “A” and “B” will be counted under both “A” and “B.” In contrast, not splitting the choices means that the pivot table will treat separate compositions of multiple choices as different choices in themselves; a record containing options “A” and “B” will not be counted under “A” or “B,” but rather, under “A, B”.
Once you’ve picked row and/or column groupings, you can choose to summarize the data by Count or by a Specific field. Count simply displays the number of records that are included in the row (and column) grouping(s).
Specific field displays the results of a summary function performed on a specific field. If you pick specific field, you'll need to select the specific field, and a type of summary function, like sum, average, or maximum. The available summary functions will depend on the field type.