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.
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.
Filter / Condition
You can use our Conditions to filter out your data:
Holistics will first applies the filter, and then pivot the data.
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 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.
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.
- Pagination Size: Number of rows displayed per page of table
- Display empty cell as 0: Enable it if you want to convert your empty cell to zero
- Row Height: Clip (single-line) or wrap (multiple-line) long-content cell
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.
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.