Pivot tables 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. A pivot table is a chart-type table that uses a intuitive drag-and-drop interface.
If you would like to visualize your search as a pivot table, click on the change visualization icon 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 drill down, or show underlying data.
If you right-click a table cell, the system displays the contextual menu, so you can drill down, or show underlying data.
If your pivot table contains more than 2 measures, 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.
Reorder your pivot table
You can restructure your pivot table by dragging and dropping the measures and attributes under the Layout area or by dragging and dropping column and row headings on the table itself.
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.
You can sort on more than one attribute at a time, as long as it works within Trends AI’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 salesand the columns byStore 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 row heading and select Sort ascending, and then add
sort by date monthlyto the search bar, Trends AI sorts only bydate monthly.
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 |
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, click on the column menu. Select 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.
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.
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 Trends AI tables, except that you cannot set different conditional formatting rules for pivot table cells and pivot table column summaries.
Select the gear icon and click on 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
toporbottomkeyword, column and row summaries are not accurate.You cannot filter aggregated columns or columns with aggregated formulas.
Pivot tables do not support cumulative functions.
Pivot tables do not support moving functions.
Subtotals and grand totals can be erroneous when one of the measures is a formula with aggregation.
Subtotals can be missing when categorizing by a formula.