We will be migrating from Ask to Discourse on the first week of August, read the details here

Ask Your Question

What is data hierarchy in pivot tables? [closed]

asked 2020-08-18 23:05:34 +0200

LobaLuna gravatar image

updated 2021-06-19 15:03:28 +0200

Alex Kemp gravatar image

Hi everybody. I'm translating the Calc Guide to Spanish, and in pivot tables chapter there is an option about hierarchy (Jerarquía) that we do not understand (me and someone else) completely. Here's the screenshot of Data field options dialog in Spanish.

image description

The guide says uses this explanation, which is kind of a circular concept ;D:

Hierarchy. Select the hierarchy to use. The pivot table must be based on external source data that contains data hierarchies.

This is a question for concept: What is a data hierarchy in this context?

  • What is a data hierarchy in this context?
edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2021-06-19 15:03:36.782434

1 Answer

Sort by » oldest newest most voted

answered 2020-08-29 20:47:08 +0200

LobaLuna gravatar image

A dear folk, Kohei Yoshida, in LibreOffice Dev mail list answered this question with a long, crystal explanation:

Data hierarchy in a pivot table is an additional layer of grouping applied to the source values of a field. I will explain this concept as follows.

First, when you initially create a pivot table with source data within your Calc document, the table output is created with however many fields you may have in the source data. Let's say you have the following field values:

Field1 1 2 3 4 5 6 7 8 9 10

then the pivot table will create a field for this Field1, initially with no hierarchy i.e. the raw values will get shown.

You can assign grouping to this field, by moving the cell cursor to where this field data is displayed within pivot table, and either press F12, or select Data -> Group and Outline -> Group from the menu. For this set of values, you can only create a numeric range group, to group the values in some specific intervals. If you have a set of date values, you can also apply date-based grouping i.e. years, quarter, months, days, and so on.

This grouping is what is referred to as hierarchy. The two are interchangeable, but in the source code, this is referred to as hierarchy pretty much exclusively.

Under normal circumstances, each field can have only one hierarchy, or none at all, as far as I know. Someone please correct me please if this is not accurate, but I'm certain this is the case.

As Steve said (prior in the maillist), this option is always grayed, because it will get active only when the field has more than one hierarchies, which, as I said above, almost never happens under normal circumstances.

Having said this, here is a scenario where that option may become active, but it needs a bit of an explanation.

Calc's pivot table implementation is split into two parts. You can refer to them as the front end and back end portions. Some people may hate these terms, but I don't care. The back-end part takes care of defining the structure of the source data, in terms of fields (or dimensions as they are referred to in the code) and the structure of each field. Each field consists of three layers - first layer > is hierarchies, the second layer is levels, and the last layer is members. A hierarchy is the type of grouping applied (i.e. value range, years, quarters etc), a level is a specific bucket within the defined hierarchy, i.e. range 1-4, year 1980, month of August etc. A member is an original value.

The front-end part receives this structure provided by the back-end, and displays it in sheets, or recently in charts, and/or provides various dialogs for tweaking the parameters. The back-end part is implemented as a UNO component, and in theory it can be swapped with one implemented ...

edit flag offensive delete link more

Question Tools

1 follower


Asked: 2020-08-18 23:05:34 +0200

Seen: 121 times

Last updated: yesterday