In this article, you will learn how to build and interpret the Pivot Table widget in your dashboards.
What is a Pivot Table?
A pivot table is a summarized or aggregated view of one or more fields in your data that allows you to compare and find patterns across discrete categories.
How to use the Pivot Table widget
To create your pivot table, click "Open pivot table", which will open the widget in the expanded view mode.
Alternatively, you can pre-populate the Pivot Table with an interesting pair that you have identified via the Top Correlations widget.
Once in the expanded view of the Pivot Table, you can select the row field and column field that you would like to see, as well as which value (frequency, NPS, rating fields, etc.), and add multiple rows or columns for the aggregations. You will also be able to adjust the color scheme if you would prefer different colors.
Date Bucketing
If you choose a Date field for either Rows or Columns you will have the option to bucket (group) dates into periods.
If "None" is selected each unique date value will act as a Segment, whereas the bucketing will allow you to display frequency or other aggregations for particular date periods. Below, the monthly frequency of Themes is shown:
Sorting
When the table has loaded, you can choose to sort it by a single column by simply clicking on the row/column name.
Choosing what to display as the cell value
The "Values" selection allows you to choose the field and measurement used to render cell values. The top dropdown lets you choose the cell data to display (such as Frequency, Sentiment, and numerical fields) while the second determines the aggregation function used to calculate the cell value.
The second dropdown provides different options based on what you've selected in the top dropdown. For example, aggregations like MIN, MAX, MEAN are only made available when you select a numerical field. COUNT simply takes the raw frequency value for that cell, no matter which field is selected.
In contrast, when NPS or NPS Impact are selected in the first dropdown, the second dropdown will not be shown, since those values are already aggregations.
Finally, when selecting Frequency, you'll notice some special options.
While COUNT simply shows the raw frequency value, the other options provide relative frequency. PERCENT OF DATA simply takes the raw frequency and divides it by the number of records in the data (taking into account any Dashboard filters). PERCENT OF ROW divides the raw frequency by the total number of records in the row; this means when PERCENT OF ROW is selected the values displayed in cells should always add up to 100% for the row.
In the below example, we can see how the Prices and Quality Themes are distributed across Metro and Regional stores with PERCENT OF ROW selected.
Special Considerations for PERCENT OF ROW
It's important to note that the PERCENT OF ROW option will always be based on the sum of counts across the entire row, which may produce unexpected results if Concepts or Themes are chosen as columns. In the below example the Themes are not mutually exclusive, nor do they provide exhaustive coverage of the Metro segment. This means when calculating the percentages the divisor can include the same record multiple times (since the Themes are not mutually exclusive) and won't necessarily include all records corresponding to the segment. This can make it hard to interpret. It's recommended to use the PERCENT OF ROW option when the columns are selected as Fields, which have mutually exclusive segments and full coverage of the data.
Applying the color palette
The color palette uses a fixed binning approach. There are 5 color values in the color gradient that are determined by taking the minimum and maximum cell values and calculating 5 equally sized bins across the range of values. For example, if the minimum value is 1 and the maximum value is 100, the bins will be 1-20, 21-40, 41-60, 61-80, and 81-100.
Updating the Pivot Table widget
To update your pivot table, change the fields and/or values in the right selector pane and don't forget to click "Update"!
Exporting the Pivot Table as an image
Like most other widgets on the Dashboard, the Pivot Table can be exported in multiple formats, including as an image. However, when you select to export the Pivot Table as an image (PNG), you'll see a dialog where you can customize which rows and columns are included. Since the Pivot Table can get quite large, this can make it easier to produce images suitable for use in reports, by hiding unimportant rows or columns. By default, all rows and columns are selected.
