Pivot table

Pivot tables enable you to explore an alternate visualization of your data in a wide, customizable table. They allow you to visualize some of your data horizontally and some data vertically in the same table. A pivot table is a chart time table that uses a drag-and-drop interface.

If you would like to visualize your search as a pivot table, click the Change visualization icon Chart type icon image near the upper right of your screen, and select pivot table. You need at least one attribute and one measure in your search.

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, expand or collapse the values in the row, or sort.

Sort by a row heading

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

Pivot table cell menu

If your pivot table contains multiple values with long names, the column header now has a horizontal scroll bar, so you can see all your measures without taking up excessive space in the pivot table.

Pivot table scroll bar gif

You can now include attributes in the pivot table cells as well as measures. This is useful for cases when you want to summarize text data such as user role, pass/fail, or other attributes. Note that when you add attributes to your pivot table cells, you will no longer see subtotals or grand totals below your pivot table.

Example of pivot table with attribute in cells

Grouping measure values Beta

To compare multiple measures in a single view you can now group measures to share an axis. Grouping measures using Values allows you to control multiple measures in pivot table charts. You can move grouped measures and position them where you want in rows and columns. You can configure where the group measures appear in the table with respect to other attributes being used in either rows or columns.

When measures are grouped using Values, all subtotals and grand totals are turned off by default. Individual subtotals and grand totals can be turned on in settings.

To enable this feature, contact ThoughtSpot Support.

Adding attribute fields to Values is not supported.
Blended Axes

Reorder your pivot table

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

By default, ThoughtSpot displays measures in columns. To display measures in rows instead, follow these steps:

  1. Select the edit chart configuration icon edit chart configuration icon image near the upper right of the Answer.

  2. Select Settings in the chart configuration panel.

  3. Under Display measures in, select Rows.

    Display measures in rows
Displaying measures in rows is only available with the new Answer experience.

Sort

To sort any column or row, right-click a row or column heading or subheading or a measure or attribute name and select Sort. You can also sort from the search bar. Type sort by <column name> [ascending | descending].

You can sort a pivot table in many different ways. The ways you can sort a pivot table differ, depending on whether you are using the classic Answer experience or the new Answer experience.

Sort with the classic Answer experience

If you use the classic Answer experience, you have the following limitations:

  • You can only sort on one attribute at a time.

  • When you sort by a measure, it sorts both columns and rows.

  • When you save a pivot table that you sorted, any sorts you added do not save.

Sort with the new Answer experience

If you use the new Answer experience, you can sort in the following ways:

  • You can sort on more than one attribute at a time, as long as it works within ThoughtSpot’s logic. See the sorting logic table.

  • When you sort by a measure, you sort rows only. This allows you to sort the rows by a measure and the columns by an attribute. For example, in the sample pivot table at the beginning of the article, you can sort the rows by Total sales and the columns by Store State.

  • When you save a pivot table that you sorted, all sorting saves as well.

  • If you sort both from the search bar and from the in-context row or column heading menus, whichever sorting is the latest overrides the other. For example, if you right-click a Department row heading and select Sort ascending, and then add sort by date monthly to the search bar, ThoughtSpot sorts only by date monthly.

Table 1. Sorting logic
Starting state Add sort on row attribute Add sort on column attribute Add sort on measure

No sorts in place

Adds sort

Adds sort

Adds sort, only across rows

One or multiple sorts exist on row attributes

Adds sort

Adds sort

Overrides previous sorts and sorts based on the measure

One or multiple sorts exist on column attributes

Adds sort

Adds sort

Adds sort, only across rows

One sort on measure exists

Adds sort. Overrides previous sort by measure

Adds sort

Overrides previous sort by measure

Sort on both row and column attributes exists

Adds sort

Adds sort

Adds sort. Overrides sort on row, retains sort on column

Sort on column attribute and measure exists

Adds sort. Overrides sort on measure, retains sort on column

Adds sort

Adds sort. Overrides sort on measure, retains sort on column

Expand or contract columns and rows

Select a column or row heading to expand it, or by right-clicking the arrow on the upper left of a row or column heading.

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

Display data as a percentage of the total

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, select the edit chart configuration icon Chart configuration icon image, and select Settings at the bottom of the chart configuration panel. Open 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.

Customize pivot summaries

You can now choose whether to display column and row summaries, and where in the pivot table they appear. To customize pivot summaries, follow these steps:

  1. Select the Edit configuration icon gear icon and click Settings.

  2. To display column and row summaries, select Show grand totals.

  3. To display column and row subtotals, select Show subtotals.

  4. 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

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 select 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 Chart configuration icon image, and select Settings at the bottom of the chart configuration panel. Select heatmap mode.

You cannot add conditional formatting to a pivot table in heatmap mode.

Number and conditional formatting

You can perform conditional and number formatting on pivot tables. Both these features work the same way they work for regular ThoughtSpot tables, except that you cannot set different conditional formatting rules for pivot table cells and pivot table column summaries. Click the edit chart configuration icon chart config icon, and select the measure you would like to add number or conditional formatting to. Refer to Apply conditional formatting and Number formatting for more information.

Pivot table limitations

The pivot table chart type has the following 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.

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

  • You cannot add conditional formatting to a pivot table in heatmap mode.

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

  • Pivot tables do not support cumulative functions.

  • Pivot tables do not support moving functions.

Was this page helpful?
×