How to reference pivot tables in a easy way?

English
#1

I found this documentation:

https://books.libreoffice.org/en/CG71/CG7108-PivotTables.html

and in theory, all is explained there, but I just don’t get it. They use names for certain types of cells that means nothing to me, and I can’t figure out which ones of those are in my pivot table.

However, even if I would know how to construct the syntax of getpivotdata, it is incredibly cumbersome and time-consuming to create it. In Excel, this can be done automatically. So if I point the data in the pivot table I want to reference, this means, at the moment, this is exactly the data I want to get, and the app is getting references to it automatically. Since I would need to get dozens of dozens such references, putting them manually would take hours of work, which is pointless, so I just get hard cell references, knowing that it could all blow up, if pivot data structure changes (like by expanding the data by clicking on plus).

Basically, hours of work just to reference the data is not acceptable and the dangers are far better than time spend. I cannot believe that there are no automatic methods of referencing in Libreoffice. If those are really absent, it’s as if there were no referencing to pivot tables in LO.

So, first, some help to understand the references what is “the field name” and “field”. In the documentation example, I see:
=GETPIVOTDATA(“Sales Value”,A1,“Employee”,“Hans”,“Category”,“Sailing”)
but since my table is different, I cannot construct working syntax on my side, even if I try to replace some of them with my names.

All I get for now is to use the most upper left cell of the pivot table in cell named “Pivot table”. The rest is just confusing. There has to be some order in which I enter various field names (why not field numbers?) to reference the data fully.

Second, would be the automatic reference to the pivot table, which is not described in the documentation, as if it was not present.

Thank you

#2
1 Like
#3

Thanks. I had to change your months references to roman values in your formula to make it work. Some dev decided that we use roman values for months and hard-coded it, although this no longer the case since 40 years or so.

Anyway, it helped me to understand the logic behind the referencing, but it still doesn’t work on my end and results in ADR. So I’m still missing something. I cannot post the same data, because this is some company data, but maybe I will figure it out with time. Thanks.

#4

I made it work, or actually, I could get the value of the pivot table using this method. However, the problem is, that this works not better than referencing simply the cell.

I created two referenced formulas. One referencing the total value of the month and the second a value of a singular item. Data are grouped by year and the month.

When the chosen month is expanded to details, then the singular reference works and shows correct value, but the reference to the month shows ADR. If I group it (so the singular values in a month are not showed), it shows ADR for singular item, but month total value is shown correctly.

So basically, similarly as I get using hard cell reference as =D6. The difference is that with hard cell reference, it shows what is in this cell, so when grouped, it changed the data and shows not what I want. When using GETPIVOTDATA, it only shows #ADR when the data in the pivot table is grouped.
This is very disappointing and not working as promised in documentation. Either I am reading it wrong, but the function was to reference the data in the pivot table, and it’s not doing this when the data is grouped, which shouldn’t matter, because the data is still there, references are correct, only visually the data is hidden. This makes no sense.

#5

getpivotdata can not show any values that are not visible in the pivot table.
Perhaps you can query from the source table. Add a calculated column with something like =TEXT($A2;“YY-MM”) which retuns something like 23-09. Then you can use SUMIF or so called “database functions” (DSUM, DCOUNT, DMAX etc.).

1 Like
#6

This beats the purpose of having pivot tables in the first place.
Grouping is a basic feature. Most data has date as a value, so we want to have them grouped by months and see totals, then we may want to expand month to see the details. Those are still nicely presented and not as dense and complicated as in source table.
Basically seems like grouping breaks important pivot data features. There is no reason why getpivotdata shows only visible data. Or if there is, there should be an alternative formula that shows the data regardless of visibility.
Anyway, it seems like pivot data is poorly implemented, compared to Excel. No matter how much I love Calc, it constantly reminds me, that Excel is far superior solution :frowning: .

#7

Well, using spreadsheets as databases beats the purpose of spreadsheets, even though everybody does it.
With the Base component, you can get reports like this:
Monthly_Base_Report.odt (87.5 KB)

#8

In my previous job, we had an analyst, who was able to download the data from the company database, import it to excel and then generate very advanced pivot tables, where we could find everything we wanted (depending on what access you were allowed).
Unfortunately, I have various sources, so I can’t get one, coherent source table, and I’m not as skilled with spreadsheets as he was.
Databases nowadays are sources for apps, while humans still prefer to work on spreadsheets.

#9

The Excel GETPIVOTDATA function, like its Calc sister, does not show totals for hidden detail rows.
The standard for GETPIVOTDATA is described here.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.