Created attachment 113189 [details] Brand new LO spreadsheet with illustrations. GETPIVOTDATA function gives bad #REF! result using "Field n / Item n" syntax. Using LO Version: 4.3.5.2 Intel Core i5-4200U CPU x64-based processor Windows 8.1 GETPIVOTDATA does not function as documented, according to LibreOffice Calc Help: You should not get “#REF!” ("Error: Not a valid reference") as long as "the pivot table contains only a single result value that fulfills all of the constraints, or a subtotal result that summarizes all matching values". I am taking "constraints" to include constraints applied using the "Field n / Item n" syntax. Or if "constraints" is referring to the "constraints" syntax branch of the Help text, then I'm talking about the "Field n / Item n" syntax branch. Works OK on Apache OpenOffice 4.1.0 Start with fresh Calc file. Enter the following: Col1 Col2 col3 bge 47 12 tpx 1.7 1.37 mrz 42 8 tpx 1.7 88.68 mrz 33 12.01 Start a Pivot Table on the above. Specify Row Fields Col1 and Col2 in that order; Data Field of col3. Make it produce the Pivot Table starting at A1 on a new sheet. GETPIVOTDATA("Col3",$A$1,"Col1","bge") yields "#REF!" - should yield 12. You should get the result 12 because there is only one "bge" row in the source data, and of course only one "bge" row in the Pivot Table. GETPIVOTDATA("Col3",$A$1,"Col1","bge","Col2",47) yields 12 as it should. But you should not have to specify the Col2 field, which is not required to pin down the qualifying row. Same approach but using Col1="mrz" requires both Col1 and Col2 to work; no complaint about that. Same approach but using Col1="tpx" with Col2 unspecified yields "#REF!" - should yield 90.05. This is because, although there are more than 1 source rows, they all have the same Col2 value and thus summarize into a single Pivot Table row.
Created attachment 113190 [details] Created new on Apache OO 4.1.0 equivalent with illustrations. Open with Apache OO and compare with other attachment "113189: Brand new LO spreadsheet with illustrations." you have opened in LibreOffice.
Confirmed with attachment 113189 [details]. LibO 4.4 & 4.5 give: bge #REF! mrz #REF! tpx #REF! mrz #REF! qws #REF! bge #REF! LibO 3.5.0 gives: bge 12 mrz #REF! tpx 90.05 mrz #REF! qws #REF! bge 12 Win 7 Pro 64-bit, LibO Version: 4.4.0.3 Build ID: de093506bcdc5fafd9023ee680b8c60e3e0645d7 Locale: fi_FI Version: 4.5.0.0.alpha0+ Build ID: 99c00b090533da9818444be2831b8da0e713e5f9 TinderBox: Win-x86@62-TDF, Branch:MASTER, Time: 2015-02-04_06:38:53 Locale: fi_FI Ubuntu 14.10 64-bit Version: 4.4.0.3 Build ID: 40m0(Build:3) Locale: en_US LibreOffice 3.5.0rc3 Build ID: 7e68ba2-a744ebf-1f241b7-c506db1-7d53735
Looks like this set of commits is where the GETPIVOTDATA formulae stopped working commit ac569ed4cf5064248b9952f182f6572f20dc9bcb Author: Kohei Yoshida <kohei.yoshida@gmail.com> Date: Mon Mar 11 17:16:54 2013 -0400 fdo#60300: Work-in-progress change to rework pivot table core. The idea is to avoid parsing the pivot table sheet output in order to calculate GETPIVOTDATA. The table outout is configurable, and it will only be more configurable in the future. The gist of my rework is to calcualte the result of GETPIVOTDATA with the internl result tree alone. Also, the same result tree can be used for drill down too, which also currently parses the table output, therefore subject to the same limitation & fragility. Change-Id: Ib0147e2aa2b710dfd627df7f535a685301214a52 ........ commit a210575baa5bc72dc0392eeb20b2da58b6cf14ae Author: Kohei Yoshida <kohei.yoshida@gmail.com> Date: Thu Apr 18 23:10:49 2013 -0400 Handle the old syntax for GETPIVOTDATA. The old syntax is Calc only (not Excel), and is specified in ODF 1.2. Change-Id: I9551d408ae136d3567375e905b1273103a8f616a
Migrating Whiteboard tags to Keywords: (bibisected) [NinjaEdit]
Investigating.
Eike Rathke committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=81f2bbc2fc42688d5f884d3dde58abe4c265694a Resolves: tdf#89186 descend into dimension children to obtain leaf result It will be available in 5.3.0. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Eike Rathke committed a patch related to this issue. It has been pushed to "libreoffice-5-2": http://cgit.freedesktop.org/libreoffice/core/commit/?id=f6b76c9d7fa989ac9475ff6d2ab45c257f96ad3e&h=libreoffice-5-2 Resolves: tdf#89186 descend into dimension children to obtain leaf result It will be available in 5.2.0.1. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Pending review for 5-1
Pending review https://gerrit.libreoffice.org/26028 for 5-1
Eike Rathke committed a patch related to this issue. It has been pushed to "libreoffice-5-1": http://cgit.freedesktop.org/libreoffice/core/commit/?id=7a3be3a229d10665d724ffc215c9dca4ab8dd0bc&h=libreoffice-5-1 Resolves: tdf#89186 descend into dimension children to obtain leaf result It will be available in 5.1.5. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.