Applicable to Sisense on Linux and Microsoft Windows
Pivot tables are one of the most useful widgets for visualizing data. They enable you to quickly summarize and analyze large amounts of data.
Pivot Tables in Windows and Linux
Windows and Linux environments use different versions of the Pivot table widget. Sisense on Windows uses Pivot1 and Sisense on Linux uses Pivot2. In general, Pivot1 and Pivot2 are the same, but there are some slight differences in features and behavior. For example, when Sisense is deployed on Linux, you can choose to render HTML in your Pivot2 cells as HTML or plain text (Pivot1 always renders the HTML). This is helpful for displaying hyperlinks, for example.
To do so:
- On the Admin page, click System Management.
- At the top-left of the screen, click System Configuration.
- In the Pivot section, toggle the switch Allow rendering Pivot Table content as HTML. When the switch is enabled, Sisense renders HTML as HTML. When it's disabled, Sisense renders HTML as plain text.
To compare the features of Pivot1 and Pivot2, see Sisense on Windows versus Linux.
Visible Table Columns (Linux)
From Sisense V8.2.1 for Linux deployments, the maximum number of visible columns in Pivot tables is set by default to 70. This saves time and improves performance.
- Your Sisense Administrator can change this default value.
- When you upgrade your Linux deployment to Sisense V8.2.1, Pivot tables with more than 70 columns will automatically reduce to 70 visible columns, according to an internal algorithm.
- These maximum column limitations do not apply when exporting Pivot tables to Excel or CSV.
To modify these maximum settings:
- In your browser, go to http://localhost:3030 to access the Configuration Manager.
- Open the Advanced Options by clicking the Sisense logo 5 times.
- In the left-hand menu, click Base Configuration.
- Scroll down to the Pivot section and click it to expand it.
- To change the maximum number of rows presented, in the pivot.limit field, enter any whole number larger than zero.
- To change the maximum number of columns presented, in the pivot.columnsLimit field, enter any whole number larger than zero.
- Click Save.
Note: These settings apply to all Pivot Tables on the account.
Adding Data to a Pivot Table
Select the data to appear in the Pivot.
- In the Rows panel, click Add + to select the field(s) whose values will be placed in the rows of the Pivot table.
Note: When Sisense is deployed on Linux, you can display HTML in your Pivot 2 cells as HTML or plain text. To select Admin > System Management, and then under the Pivot area toggle the switch Allow rendering Pivot Table content as HTML. When the switch is enabled, Sisense renders HTML as HTML, and when it's disabled, Sisense renders HTML as plain text.
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:
- 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 drag and drop the fields that you added to Columns to the Rows area and vice versa.
- (Pivot1 only) In the Values panel, select a field whose values will appear in the Rows and Columns of the Pivot table.
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:
- 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.
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.
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 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:
To calculate weighted averages in the grand and Subtotals:
- The Subtotal by average takes the values in the rows above it, 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.
For a more detailed example, see here.
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.
- Colors: Select the properties in the table to which you want to add color.
Select how the rows and columns of the Pivot table are highlighted.
Conditional formatting can be used for exception highlighting in a Pivot table. For example, as shown below:
See Selecting Colors in Widgets for more information.
Note: For more styling options, including font style and colors, see this article.
- 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 following 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.
- The maximum number of rows per page is 200 rows