Pivot table

Pivot tables are charts that enable you to explore an alternate visualization of your data in a wide, customizable table. With pivot tables, you can use the same table to visualize some of your data horizontally, and some data vertically.

If you would like to visualize your search as a pivot table, click on the change visualization icon icon chart type 10px near the top right of your screen, and select pivot table. You need at least one attribute and one measure in your search.

You can restructure your pivot table by dragging and dropping the measures and attributes under edit chart configuration icon gear 10px, or by dragging and dropping column headings on the table itself.

If you right-click a row or column heading, the system displays a contextual menu, allowing you to exclude or include values, drill down, show underlying data, SpotIQ analyze, or sort.

Sort by a row heading
Sort by a column heading

If you right-click a table cell, the system displays the contextual menu, so you can exclude or include values, drill down, show underlying data, or SpotIQ analyze.

Pivot table cell menu

Expanding or contract columns and rows

Click on a column or row heading to expand it. Additionally, you can expand or collapse all columns and rows by right-clicking the arrow on the top left of a cell.

Expand or collapse all

When you pin a pivot table to a Pinboard, it retains your expansion settings.

Display totals

You can see your data as a percent of a row or column total, or as a percent of the grand total. Grand totals aggregate all the data in your pivot table.

To see your data as a percent, click the edit chart configuration icon gear 10px icon, and click settings at the bottom of the chart configuration panel. Click on the dropdown menu to choose whether you want to see your data as a percent of a row or column total, or as a percent of the grand total.

See your data as a percent

You only see 100% value when grand total rows is enabled. When it is disabled, the rows and columns have no parental total column, so the percentage cannot be calculated. All intermediate totals (columns or rows) display the percentage values calculated with respect to their parent. For each inner summary (column or row) the parental total values are assumed to be 100% internally.

Select Pivot Summaries in the Settings menu to see row and column totals.

See row and column summaries

Format row headers

You can view your row headers in a more compact (tree) layout. To turn on compact row headers, select Compact row headers in the Settings menu.

Compact row headers

You can change the type of total shown for an aggregated measure from the header menu that appears when you click on a header’s more icon:

Change aggregation type

Heatmap mode

The heatmap functionality allows you to see which measures contribute more to the total than others within the table.

Heatmap mode

To turn on the heatmap, click the edit chart configuration icon gear 10px icon, and click settings at the bottom of the chart configuration panel. Select heatmap mode.

Pivot table limitations

The pivot table chart type has these limitations:

  • The pivot table displays a maximum of 100k rows. If your query returns more than 100k rows, you cannot visualize it with a pivot table.

  • You cannot use conditional formatting.

  • Pivot tables do not support use of group aggregation functions.

  • If the query the pivot table is based on contains the top or bottom keyword, column and row summaries are not accurate.

  • You cannot filter aggregated columns or columns with aggregated formulas.

Was this page helpful?
×