Pivot Table

Pivot Table, which is similar to Excel Pivot, is a visualization type that aggregates data in a meaningful way by combinations of dimensions and measures.

It is particularly useful if you still want to present your data in table form but with more meaningful aggregations like sum, average, min, max...

In general, pivoting data is a useful and essential technique in the business world, so we have written a dedicated blog post detailed explaining What is a Pivot Table and how to use it.

Basic Setup

It is very easy to create a Pivot Table in Holistics. A Pivot Table has to contain at least 1 row (or column) field and 1 Value field:

• In Rows and Columns fields, select the dimensions that you want to aggregate your data on.
• In Values field, select the column that you want to aggregate. Note that the Values field always contains an aggregation.

Show / Hide Columns

You can hide a Row Field column from a pivot table in two ways:

1. Click on any Row Fields in the Visualization Settings and select Hide field.
2. Click to open the Context Menu (arrow icon) of any Row Field column in the pivot table and choose Hide in view.

To unhide a Row Field column, click on that field in the Visualization Settings and select Show field.

Note

• Currently, we only support hiding Row Field columns.
• This is an aesthetic feature, not a security feature. Hiding a column will remove it from the user’s report view and export, but the data is still accessible and not restricted in any way.

Filter / Condition

You can use our Conditions to filter out your data:

caution

Holistics will first apply the filter, and then pivot the data.

Sort

Sort function in Pivot allows you to get insights by re-ordering your pivot table in a more meaningful way. There are two ways to sort data in the Pivot Table:

• Go to Pivot Table → Add field to sort, and select sort order on Viz Settings → Get Result; or
• Click on the Angle icon on Pivot Header

Note that you are only able to sort fields that are used in the Pivot Table.

Totals and Sub-totals

Grand totals

Grand totals are the final amounts aggregating all values in a pivot table:

• Column Grand total is the row that displays summary totals calculated against all rows.
• Row Grand total is the column that displays summary totals calculated against all columns. To enable Grand totals for Pivot Table, you can open the Styling tab > Enable Row total / Column total toggle.

Subtotal

Similarly, Holistics also supports Sub-totals in the Pivot Table. Sub-totals are the amount aggregating values in a specific sub-category.
To enable Sub-totals for Pivot Table, you can open the Styling tab > Enable Sub-totals toggle.

Styling options

Display

• Pagination Size: Define the number of rows displayed per page in the table.
• Display empty cell as 0: Enable it if you want to convert your empty cell to zero.
• Row Number: Display the row number at the beginning of each table row.
• Row Height: Clip (single-line) or wrap (multiple-line) long-content cell. We recommend a Single-line option for better performance.
• Column Freeze: Specify how many Row Field columns to be frozen from the left.

Conditional Formatting

Conditional Formatting in the Pivot Table works the same as in Table.

How Holistics calculates Totals / Sub-totals

Technically, the Total concept refers to the total value of a measure within a category (a category is a group of zero or more dimension values).

• When the category has all the dimensions of the Explore, Total is the Measure (Value field) itself
• When the category has fewer dimensions than the Explore, Totals are the Sub-totals. The biggest Subtotals are called Row Totals or Column Totals according to their placement in the result.
• When the category has zero dimensions, Totals are the Grand totals. Thus, Holistics calculates Totals by running additional queries that contain fewer dimensions than the original Explore.

FAQs

1. I want to enable/disable Row sub-totals and Column sub-totals separately. How should I do?
Currently, we haven't supported turning on/off these two types of sub-totals independently. If it has a high impact on your business, you can share with us more details about your case via a support ticket.

2. I want to use some Pivot functions, such as collapsing rows/columns, and resizing..., but I can find them in Holistics. How can I use it?
Currently, we haven't supported these functions yet, but our team is actively developing them. If it has a high impact on your business, you can share with us more details about your case via a support ticket.