Pivot tables in Analytical Designer can have up to 20 attribute rows and 20 attribute columns to refine data in your insight. The data is then merged according to the attribute order in the Rows/Columns sections.
Pivot tables have the following sections: Measures, Rows, Columns, and Configuration.
Pivot tables can also:
- Display the values as a percentage.
- Compare your data to the previous period or same period of the previous year.
For details, see Time over Time Comparison.
Group data when sorted by the first item in the Rows section.
For information about common characteristics and settings of all insights, see Insight Types.
If you create a pivot table, you cannot open the insight as a report. The Open as Report option is disabled.
Tables automatically adjust the width of all the columns according to the cell content. The size is calculated according to the content in the header of the column that represents the lowest level of the grouped attributes.
To disable the automatic resizing, set the
enableTableColumnsAutoResizing platform setting to false. For details, see Configure Various Features via Platform Settings.
You can also resize each column manually by dragging the edge of the column in a header.
- To resize all columns in your insight at once, hold the Windows/Command button while dragging.
- To resize all columns of one measure at once, hold the Alt/Option button while dragging.
- Double-click the edge of the column to resize to the default size of the column.
To disable manual resizing, set the
enableTableColumnsManualResizing platform setting to
false. For details, see Configure Various Features via Platform Settings.
On KPI Dashboards, small tables are automatically resized to fit the whole width of the widget. For details, see Resize widgets in Edit KPI Dashboards.
Multiple date attributes
To add multiple date attributes to pivot tables, you must have the
enableNewADFilterBar platform setting set to
true. For details, see Configure Various Features via Platform Settings.
Pivot tables support multiple date attributes. For each date attribute, you can select a different date dimension to analyze your data with various date contexts.
For example, you can display when individual customers ordered your products and when the invoices were paid.
When you filter the whole insight by date, you can select any dimension that is available.
When comparing your data over time, the date filter must have the same dimension as one of the date attributes in your insight. For additional information about comparing your data, see Time over Time Comparison.
You can aggregate data in a table using the following functions:
- Rollup (Total)
For information about the rollup function, see Aggregate Table Data.
Create a pivot table in Analytical Designer.
For details, see Create Insights.
To add table totals, the table must include at least one item in the Measures section and one item in the Rows section.
- Hover the mouse over a column header.
A burger icon displays on the left side of the header.
Click the burger icon and select a function.
A new row with the function name and appropriate values under the column is displayed.
You can add functions for all columns or for individual attributes or measures. Hover your mouse over column headers to display the burger icon.
The following image shows the
Sumfunction added in the Year attribute and the
Avgfunction added in the Checkouts measure.
If you have multiple items in the Rows section, you can apply the function to all rows (grand total) or to individual attributes from the Rows section (subtotals).
To delete a function, click the burger icon and click an already-selected function (with a tick sign) to hide the function row.
For information about tables in the Reports section, see Working with Tables.