Pivot

Pivot tables are one of the most useful widgets for visualizing data. They enable you to quickly summarize and analyze large amounts of data.

Adding Data to a Pivot Table

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.

    Note: When Sisense is deployed on Linux, you can select to display HTML in your Pivot 2 cells as HTML or plain text, see Configuring Pivot Table Limits.
    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, formulate a measure 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.

Grand Totals

Grand totals aggregate the rows in the pivot table.

To add grand totals:

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:

Embedding Images

Note: For Linux systems starting with L2021.5, the best practice is to disable the embedded images plug-in and use the following feature for embedded images. The embedded images plug-in will be deprecated in the upcoming L2021.7 release.

You can show images in the pivot table if there is a column with the images' urls in the table's data model.

To embed images:

  1. In the Rows panel, click Add + and select the field that contains the images' url.
  2. Click the field's menu and select Show as Images.

If you store images under a separate domain from the one used for Sisense, you must configure the CORS setting so Sisense can access the image files. For more information, see Cross Origin Resource Sharing.

Note: In some instances, images will not appear in the widget’s pdf output if the table holds many images and there is a slow connection to the URL where the images are stored.

Designing the Pivot Table

Fine-tune the appearance of the PIVOT table, using the following tools.

Exception Highlighting

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.

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 and select System Management.
  2. Click Configuration.
  3. Scroll down to the Pivot Table section and expand it.

After you have configured these settings, scroll to the bottom of the System Configuration page and click Save.

Additional Pivot Table Limit Information

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 following properties of a pivot table increase the probability of reaching a timeout when dealing with very large tables:

To successfully export pivot tables, split large pivot table with many columns to separate smaller pivot tables.