Pivot tables provide a flexible, customizable way to explore your data in a wide table format. They allow you to display attributes and measures across both rows and columns using a drag-and-drop interface. Pivot tables are ideal when you need to compare grouped data from multiple perspectives in a single view.
In this article:
Overview
Grouping measure values
Reorder & Sort Your Pivot Table
Format Row Headers
Heatmap mode
Number and conditional formatting
Additional Resources
Overview
To visualize your search as a pivot table, select the Type icon in the upper-right corner of the screen and choose Pivot table. Your search must include at least one attribute and one measure.
Pivot tables support right-click contextual menus for both row and column headers, allowing you to include or exclude values, drill down, show underlying data, or sort.
If your pivot table contains multiple measures with long names, horizontal scrolling is enabled in the column header to preserve readability. You can also place attributes in pivot table cells to summarize text-based values such as user role or pass/fail status; however, when attributes are added to cells, subtotals and grand totals are no longer displayed.
The pivot table chart type has the following limitations:
- Maximum 100,000 rows.
- If the query the pivot table is based on contains the top or bottom keyword, column and row summaries are not accurate.
- Cannot filter aggregated columns.
- Does not support cumulative or moving functions
- Subtotals may be inaccurate with aggregated formulas
- Subtotals can be missing when categorizing by a formula.
- Conditional formatting is not supported in heatmap mode.
Grouping measure values
To compare multiple measures in a single view, you can group measures using the Values chip in Layout. Grouped measures can be positioned in either rows or columns and rearranged alongside attributes to control how they appear in the table.
When measures are grouped using Values, subtotals and grand totals are turned off by default, though they can be enabled individually in settings. Only measures can be added to the Values section; attributes are not supported.
Reorder & Sort Your Pivot Table
You can restructure your pivot table by dragging and dropping measures and attributes in the chart configuration panel or directly within the table headers. By default, measures appear in columns, but you can display measures in rows by navigating to Display Settings Display measures in Rows.
To display measures in rows instead, follow these steps:
-
Click the Display button on the right panel of the Answer.
- Under Display measures in, select Rows.
Sorting can be applied by right-clicking any row or column header and selecting Sort, or by using the search bar with syntax such as sort by <column name> ascending or descending.
When sorting by a measure, only rows are sorted, allowing you to independently sort rows by measure and columns by attribute.
If sorting is applied both in the search bar and via header menus, the most recent sorting action overrides the previous one, and saved pivot tables retain all applied sorting.
Select a row or column header to expand it, or use the arrow in the upper-left corner of the header to expand or collapse levels. When pinned to a Liveboard, pivot tables retain their expansion settings.
Pivot tables can display values as a percentage of a row total, column total, or grand total. To enable this, open Display> Display format and choose the desired percentage option.
Grand totals must be enabled to display a full 100% value. Intermediate totals calculate percentages relative to their parent total, which is internally treated as 100%.
You can choose whether to display column and row grand totals and subtotals, as well as control their position at the top or bottom of the table. These settings are available under Display Summary.
Pivot summaries are calculated as separate SQL queries and use the aggregate defined for the column. The total’s aggregation type cannot be customized directly; for example, if a column uses count(customerID), the total will also be a count.
To customize pivot summaries:
-
Click the Display button on the right panel of the Answer.
- To display column and row summaries, select Show grand totals.
- To display column and row subtotals, select Show subtotals.
- To control whether the grand totals appear at the top of the chart or at the bottom, select Top or Bottom under Summary position.
Format row headers
Pivot tables support a compact tree layout for row headers. To enable this, open Display and select Compact row headers.
You can also modify the total type for an aggregated measure using the header menu accessed from the header’s more options icon.
Heatmap mode
Heatmap mode visually emphasizes higher and lower values within the pivot table using color intensity. To enable heatmap mode, open the Display panel and check Enable under Heatmap mode.
Conditional formatting cannot be applied while heatmap mode is enabled.
Number and conditional formatting
Pivot tables support number formatting and conditional formatting similar to standard tables. However, you cannot define separate conditional formatting rules for pivot cells and pivot column summaries.
To format a measure, select the Column icon and choose the measure you want to format.
To format a measure, select the Column icon and choose the measure you want to format.
Blended axes allow you to group measures using the system-defined Values chip, which behaves like an attribute containing the selected measures as unique values. Measures can then be placed across rows or columns, enabling more advanced layout combinations and comparisons.
To create blended axes:
- In chart view, select the Layout button.
- Drag one or more measures to the Values section.
- Drag the Values chip to either the Rows or Columns section.
- To view your changes, click the Update button.
- Your pivot table reloads with your blended axes.
- You can only use measures in the Values section.
Additional Resources
ClearInsights Max: Chart Types Available
ClearInsights Max: Edit Table Options
Comments
Please sign in to leave a comment.