Pivot tables are one of the most useful widgets for visualising data. They quickly summarize and analyse large amounts of data.
Adding data
Select the data to appear in the pivot.
1. In the Rows panel, click Add + to select the field(s) whose values will be placed in the rows of the Pivot table. You can add up to 20 fields in the Rows panel.
When more than one Row is selected, the Rows are broken into sub-rows (groups). For example, the following shows Condition added as a second Row:
2. In the Columns panel, click Add + to select the field(s) whose values will be placed in the columns of the Pivot table. You can add up to 3 fields in the Columns panel.
You can drag and drop the fields that you added to Columns to the Rows area and vice versa.
3. (Pivot1 only) In the Values panel, select a field whose values will appear in the Rows and Columns of the Pivot table. You can add up to 20 measures to the Values panel.
Tip: Right-click the value to add data bars to your pivot table.
Grand totals and subtotals
You can add grand totals and subtotals to your tables, and define how to calculate subtotals.
To add grand totals and subtotals to rows:
1. Click on the menu of the Row in the Data Panel, or right-click on the row header in the widget, and select Grand Totals or Subtotals.
Note: Subtotals are enabled only if you have more than one row in your table.
2. If you selected to display Subtotals, select the method by which to calculate them. Click the menu of the Value in the data panel, and then click Subtotal by and select the method.
Note: You can choose different Subtotals in each field. To do this, click the header of the desired value in the table, click Subtotal By and select the method.
The default option is Auto, which aggregates all the data, sending a separate query to the data model. This means that Sisense applies the same formula set in the measure to the entire data. The other custom options (Sum, Min, Max, etc.) calculate only the values in the rows above the Subtotal, based on the data presented in the Pivot table, in place of the original formula set in the measure.
Consider the following:
In the top example, using the Auto option, the Subtotal for average revenue aggregates all the sales and revenue data. This represents a true Subtotal of the average revenue for the Asia region.
In the bottom example, Average is used to calculate the Subtotal, and therefore calculates the average of all the average revenue values in the rows above the Subtotal.
Distinct totals in pivot tables
The process of denormalization of the data sometimes creates duplicates. Duplicates are not a big problem when processing text, because you can always "count distinct". However, they are a big problem when processing numbers because you cannot "sum distinct".
There is a new Total calculation type - Distinct Totals. When enabled, duplicate values are eliminated before the aggregation is calculated.
Analytical Engine calculates the totals, based on distinct values (eliminating any many-to-many impact, and avoiding counting values multiple times.)
The administrator can set the Enable Distinct Total Per Measure default behaviour in Admin > System Management > Configuration > (5 clicks on the logo) > Base Configuration > Pivot Table.
The dashboard designer can set the required behaviour for each pivot table.
Example 1
There are two tables in the database - FILMS and GENRES.
|
FILMS |
|
|
ID |
DURATION |
|
1 |
100 |
|
2 |
200 |
|
3 |
300 |
|
GENRES |
|
|
FILM |
GENRE |
|
1 |
Comedy |
|
1 |
Horror |
|
2 |
Drama |
|
3 |
Comedy |
|
3 |
Drama |
In the dashboard, a pivot table is created, based only on the FILMS table:
|
FILM DURATION |
|
|
1 |
100 |
|
2 |
200 |
|
3 |
300 |
|
TOTAL |
600 |
Another pivot table is created, based on the relationship between the FILMS and GENRES tables, where:
- FILMS = aggregated table (parent)
- GENRES = attribute table (child)
|
FILMS DURATION AND GENRE |
|
|
|
GENRE |
FILM ID |
DURATION |
|
Comedy |
1 |
100 |
|
Comedy |
3 |
300 |
|
Comedy Subtotal |
400 |
|
|
Drama |
2 |
200 |
|
Drama |
3 |
300 |
|
Drama Subtotal |
500 |
|
|
Horror |
1 |
100 |
|
Horror Subtotal |
100 |
|
|
TOTAL |
?? |
|
Grand total duration calculation:
- Auto = 1000
- Manual Sum = 1000
- Distinct total = 600
Rows in the aggregated table that do not have a matching row in the attribute table are not counted in the total calculation.
A measure from the aggregated table (FILMS) is NOT duplicated in the sum, regardless of the number of times it appears in the lower granularity attribute table (GENRES).
Example 2
Consider the table below. The intention is to see the annual growth rate for 2010.
Distinct Totals returns 3.10% because it calculates the average for the entire period (the whole of 2010) against the database itself and can therefore compare the ending number against the starting number.
Auto and Manual Totals return 38.17% because they add the monthly growth rates returned and don’t have access to the whole year’s data.
Grand totals
Grand totals aggregate the rows in the pivot table. To add grand totals:
Click on the menu of the row in the data panel, or right-click on the row header in the widget, and then click Grand total.
If defined, the Grand Totals is affected by Subtotals. In the following example, the Grand Total for Average Sales is 10, representing the average sales for two regions (Asia and Europe). In the example below, Average Sales has a Subtotal (see above) set to Sum, and therefore the Grand total is the total of the rows above.
Rectifying problems with grand totals and subtotals
In some cases, it may seem that the grand or subtotal are calculated incorrectly. The reason may be just the selected calculation method.
In other cases, you may want to calculate weighted averages in the grand and subtotals, instead of the arithmetic mean that is used by the system.
To rectify problems with Grand Totals and Subtotals change the Subtotal By from Auto to Average:
To calculate weighted averages in the grand and subtotals:
The subtotal by average takes the values in the rows above it, and therefore cannot be set to show a weighted (multi-pass) average. A way to achieve this is to change your formula in a way that when subtotals are set to auto, it will show the weighted average.
For example, see the below use case (from the Sample Healthcare dashboard), using weighted aggregation:
Two connected fields are used here: division.ID and Rooms.Division_ID. The two fields are used from the dimension table in 'Rows' and its equivalent in the fact table in the formula.
In this example, we count beds per division, sum the results, and divide this result by the sum of rooms per division. When looking at a specific division, it is the same as counting beds/rooms, but as a total, it is 10/59 (sum/sum) - the weighted average.
Designing the Pivot Table
- Fine-tune the appearance of the PIVOT table, using the following tools.
- Page Size: Specify how many rows appear in each page. Paging options are provided accordingly. The maximum number of rows per page is 200 rows.
- Colours: Select the shading properties for the Pivot table's row and column elements.
- Manual Row Height: Enable and enter a Row Height value (in pixels). If you are showing embedded images in your table, the images are resized to fill the row height and may be cropped because the column width is fixed.
- Auto Height: Automatically sets the Pivot table's widget height based on the content of a single page in the table.
- Page Size: Specify how many rows appear in each page. Paging options are provided accordingly. The maximum number of rows per page is 200 rows.
Exception Highlighting
Conditional formatting can be used for exception highlighting in a Pivot table.
For example, as shown below:
Configuring Pivot Table Limits
To improve performance and limit resource usage, you can limit the amount of columns and rows displayed and define the number of rows that can be queried by a Pivot Table widget.
You access these settings as follows:
1. In Sisense, open the Admin page.
2. On the left side, under the System Configuration section, select System
Management.
3. Click Configuration.
4. Scroll down to the Pivot Table section and expand it.
- Allow rendering Pivot Table content as HTML: The default is to render HTML as plain text. When enabled, Sisense renders HTML as HTML. If you enable this option, click Save at the bottom of the page and click OK to apply the changes and restart the relevant Sisense services.
- Maximum number of columns to display: Limits the number of columns presented at the end of each page of the Pivot Table on the dashboard. The default number of columns is 70.Limiting the number of columns ensures that the number of cells on each page does not delay the rendering process for presenting a single pivot page.
- Pivot Query LIMIT: Enter any whole number larger than zero to set the maximum number of rows to include in a query results.
After you have configured these settings, scroll to the bottom of the System
Configuration page and click Save.
Limitations
- Sisense supports the exporting of pivot tables of up to 1.5 million cells. Attempting to export a higher number of cells might result in a timeout. The below properties of a pivot table increase the probability of reaching a timeout, when dealing with very large tables:
- Sub totals or Grand totals
- Complex formulas in Values
- Data Security rules
- Widget-level filters
- To successfully export pivot tables, split large pivot table with many columns to separate smaller pivot tables.
- In the PDF Report Settings, the first 14 pages of your Pivot table are displayed in the preview window, however, when you export your Pivot table to PDF, the entire table is included, up to 1,000 rows on multiple pages.