Bug 145966 - [PIVOTTABLE] Add =GETPIVOTDATA(...) formula when referencing data inside a pivot table
Summary: [PIVOTTABLE] Add =GETPIVOTDATA(...) formula when referencing data inside a pi...
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
unspecified
Hardware: All All
: medium enhancement
Assignee: Not Assigned
URL: https://help.libreoffice.org/latest/e...
Whiteboard:
Keywords:
Depends on:
Blocks: Pivot-Table Calc-UX Calc-Function
  Show dependency treegraph
 
Reported: 2021-11-30 13:16 UTC by Olivier Hallot
Modified: 2022-08-23 08:29 UTC (History)
2 users (show)

See Also:
Crash report or crash signature:


Attachments
Image of the formual. INFODATDOSTABELADINÂMICA is the localized name for GETPIVOTDATA (2.75 KB, image/png)
2021-11-30 13:16 UTC, Olivier Hallot
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Olivier Hallot 2021-11-30 13:16:53 UTC
Created attachment 176594 [details]
Image of the formual. INFODATDOSTABELADINÂMICA is the localized name for GETPIVOTDATA

Reporting an community demand for pivot tables, available in MS Excel and missing in Calc.

1) Have a pivot table in your spreadsheet
2) place cursor *out* of the pivot table, say "X1"
3) type = and click in one of the total row/columns of the pivot table
4) Cell X1 has now the formula '=GETPIVOTDATA(...)' with arguments taken from the source pivot table.

Why this is a nice feature?

a) it is a extremely user friendly way to create the right formula for GETPIVOTDATA
b) is the pivot data is updated, the formula stays valid.

Actual results:
X1 receives a cell reference of a pivot table cell

Expected result
X1 receives =GETPIVOTDATA(...) with arguments picked from the source pivot table.
Comment 1 Roman Kuznetsov 2021-12-02 19:24:50 UTC
Fully agree, it is a really useful feature
Comment 2 Heiko Tietze 2021-12-16 11:53:46 UTC
Sounds okay to me to insert the GETPIVOTDATA() formula instead the actual value.

But I wonder what type of formula to use, see online help. This example

Cat 1	Sum - A
1	1,73490402254057
2	1,18748293692689
3	1,65489692181237
Total Result	4,57728388127982

shows per =GETPIVOTDATA(A1:B5;3) the value of B3. But I fail to do the same with B3 or in case of more than one result column. The formula GETPIVOTDATA(TargetField; pivot table[; Field 1; Item 1][; ... [Field 126; Item 126]]) is unclear to me, and the help not informative.

So the question is a) does inserting a formula helps to understand how to use it, b) what benefit does this have (the result is the same as with =B3), and c) what type of formula to use.

Me struggles with b) and somewhat a).
Comment 3 Heiko Tietze 2022-08-23 08:29:20 UTC
(In reply to Heiko Tietze from comment #2)
> So the question is a) does inserting a formula helps to understand how to
> use it, b) what benefit does this have (the result is the same as with =B3),
> and c) what type of formula to use.
> 
> Me struggles with b) and somewhat a).

Answering myself: Benefit of =GETPIVOTDATA("<Col>";A1) over =A1 is clearly to keep the value even when the table changes. Plus, it educates the users to use the right functions.

So let's do it.