Created attachment 84797 [details] sample data, two pivot tables with the same use of GETPIVOTDATA function, one OK, one KO Problem description: GETPIVOTDATA returns error #REF! when more than 1 line field is used Steps to reproduce: 1. create some data (see attachment) 2. create pivot table with just one line field, one column field and use GETPIVOTDATA to test all is OK 3. just add a second line field in the pivot table : the function then return error #REF! This was working fine with version 4.0 Bug appeared with 4.1.0 release, replicated with 4.1.1.1, Build ID: a990db030b8125868501634ff662be1d89d0868) Operating System: Windows 7 Version: 4.1.0.4 release
Created attachment 85092 [details] Modified sample Hi Guillaume, thanks for reporting. There several results for your criteria in the formula: =GETPIVOTDATA("How long (h)";F12;"What";"activity 1") what must be for you. You need to set up a criteria searching for an unique result, like: =GETPIVOTDATA("How long (h)";F12;"Who";"bill";"What";"activity 1") It is possible what you want, moving the "What" field to the first column and edit the layout to set up "What" to sum for it.
Not a bug for me. Change to RESOLVED NOTABUG, please if you are not agree reopen it.
Hi Mariosv, I don't understand why the function GETPIVOTDATA has now a different behaviour, depending on the layout of the pivot table. Now, it seems that we must specify as many search criteria as we have line fields displayed. In previous versions, it was not the case. --> can this be considered as a regression ?
Sorry Gillaume, but the result is the same for me with 3.5.7.2 and 4.0.5.2. In J5: 4, in J12: #REF!. If there are several results for the criteria, it is no possible that GETPIVODATA() can know which of them must return. That is the case in J12. Please what are the version working fine for you?
Created attachment 85169 [details] screen capture of a case that worked with 4.0 This file was made at the end of May, with version 4.0.3 I think. The formula in cell P6 worked at that time, and returned 19908. Here, we have 3 line fields ("Groupe", "Recept" and "Nom") and 1 column field, and the formula just queries for the total of data field "somme - exped" for one particular value of "recept". In short, the formula should return the total value that is displayed in cell H6.
Maybe something change form when was done, try moving "Nom" before "Recept." Your first attached file as I mention in comment #4 doesn't work in 3.5.7.2 neither 4.0.5.2
Created attachment 85365 [details] tescase under 3.5.7.2 - linux Test case with version 3.5 : The call to function GETPIVOTDATA is made specifying only one line field and a given value. And it works... In fact, in this case, the function return a value even if "group" and "who" have not been specified, because there is no ambiguity. Testing the same with version 4.X.X fails...
Hi Guillaume, Works for me in: 3.5.7.2 4.0.5.2 does not work in: Version: 4.1.2.0.0+ Build ID: aa9bef8271ed50a397c959ed53c91ee44b3dcb1 TinderBox: Win-x86@6-debug, Branch:libreoffice-4-1, Time: 2013-09-03_11:55:14 But I am not sure what it the right way. Duplicating the 101 in source table and updating the PT, all versions results in #REF! because there is an ambiguity with more than one row in PT with 101. In any case seems a regression if not is a design change.
Unfortunately the previous behavior of accepting this pattern was a mistake. It's supposed to return #REF! when the filter rule may have more than one hit, as in this case. So, technically this is not a regression. The previous GETPIVOTDATA implementation unfortunately didn't throw an error even when it should have. The use case reported in this bug report is one example of that.
Khoei, thanks for explaining what should be the behavior, it was my first suspicion, comm#1, but always a problem for users when a behaviour change, specially in case like this, where the correction implies a change from work to non work. If was possible a way doing easy to find information about this kind of corrections, e.g. a Behaviour changes at the end of the list in the help contents. Changed the status to RESOLVED NOTABUG.