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 detailedy explaining What is a Pivot Table and how to use it.

Basic Setup

It is very easy to create 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 aesthetics 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 applies 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 Pivot Table:

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

Note that you are only able to sort fields that are using in 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.

Sub total

Similarly, Holistics also supports Sub-totals in 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 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 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 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 less dimensions than the Explore, Totals are the Sub-totals. The biggest Sub-totals 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 which contain less dimensions than the original Explore.

FAQs

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

2. I want to use some Pivot functions, such as collapsing rows/columns, 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 high impact on your business, you can share with us more details about your case via support ticket.