Created attachment 44391 [details] Issue_GETPIVOTDATA_date My explanation refers to the attached OD-Spreadsheet Issue_GETPIVOTDATA_date.ods == Explanation == Issue_GETPIVOTDATA_date.ods contains one Datapilot sheet with two datapilots, which refer to the two source sheets. The source sheets contain the very same data, except that the first column has different date formats: • Date-long: DD.MM.YYYY • Date-short: DD.MM.YY I use the following formulas to access the information in the datapilots: =GETPIVOTDATA($A$2;"Rechnungs-Datum[26.01.10]") =GETPIVOTDATA($A$2;"Rechnungs-Datum[26.01.2010]") == Problem == The mentioned formulas should work for both datapilots, since the source data is the very same. However only the formulas work where the formats match, this is not expected as the display format shouldn't matter. An aggravating factor (or actually the worst part) is that in the Formula Bar in the source sheets the data always in the DD.MM.YYYY format is displayed, so to figure this very little difference out, can take *quite* a while... A quick fix should be at least to mention this in the help file.
Also notice that the (display) format in the datapilot(s) itself, is completly different (MMMM YYYY) and doesn't matter at all - this how it should work. But if you look in the formula bar you still see the DD.MM.YYYY format in *both* datapilots and then it is not expected that a GETPIVOTDATA with DD.MM.YYYY does not work. If(!) the source format should matter, than this should be displayed accordingly in the formula bar of the datapilot.
I guess this is my area.
@kohei Why did you removed yourself from the assignee, is this no longer your area?
(In reply to comment #3) > @kohei > Why did you removed yourself from the assignee, is this no longer your area? http://lists.freedesktop.org/archives/libreoffice-qa/2013-January/003492.html
** Please read this message in its entirety before responding ** To make sure we're focusing on the bugs that affect our users today, LibreOffice QA is asking bug reporters and confirmers to retest open, confirmed bugs which have not been touched for over a year. There have been thousands of bug fixes and commits since anyone checked on this bug report. During that time, it's possible that the bug has been fixed, or the details of the problem have changed. We'd really appreciate your help in getting confirmation that the bug is still present. If you have time, please do the following: Test to see if the bug is still present on a currently supported version of LibreOffice (4.4.0.3 or later): https://www.libreoffice.org/download/ If the bug is present, please leave a comment that includes the version of LibreOffice and your operating system, and any changes you see in the bug behavior If the bug is NOT present, please set the bug's Status field to RESOLVED-WORKSFORME and leave a short comment that includes your version of LibreOffice and Operating System Please DO NOT Update the version field Reply via email (please reply directly on the bug tracker) Set the bug's Status field to RESOLVED - FIXED (this status has a particular meaning that is not appropriate in this case) Thank you for your help! -- The LibreOffice QA Team This NEW Message was generated on: 2015-02-19
Confirmed. F13, F29 and F30 display #REF! Win 7 Pro 64-bit, LibO Version: 4.4.1.2 Build ID: 45e2de17089c24a1fa810c8f975a7171ba4cd432 Locale: fi_FI
GETPIVOTDATA() uses a text string based interface to obtain matching member results. This depends on the formatted data (actually the same result entries that can be seen in the filter lists). This is matched against the [26.01.2010] text query, for example. To make matters worse, date formatting depends on the locale, so if not formatted to a specific locale that is also used in the query, these queries will not work in another locale that uses different separators or DMY order. For example in en-US all four queries deliver #REF! Workaround: to be on the safe side format dates in ISO 8601 notation, i.e. using YYYY-MM-DD format code on date data and also in the query, those deliver equal results in all locales. This will need some deep digging and changing things to provide also internal type information and actual values instead of just formatted display strings. I'm wrapping my head around this..
Eike Rathke committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=f88f6bcfce233c336c9025d466c5c2fb0b671de3 Resolves: tdf#35247 introduce a locale independent numeric field filter 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.
Note that even with this change loading the test case document in a locale that does not use the D.M.Y date notation, e.g. M/D/Y instead, will result in #REF! The query though is now independent of the actual cell format applied to the source data, so specifying the ISO 8601 notation only in the query works =GETPIVOTDATA($A$2;"Rechnungs-Datum[2010-01-26]").
Eike Rathke committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=1bcfa6212553b1d130036f0d1e5bc0dc0692b706 attempt to obtain numeric constraint values for GETPIVOTDATA(), tdf#35247 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 "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=bd8d91e9deed91ca63b8149b1c32a2ce2c962c6b use the new type information we now transport, tdf#35247 related 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.
With the latest change it is now possible to use the preferred form GETPIVOTDATA(TargetField; pivot table; [ Field 1; Item 1; ... ]) with, for example, the DATE() funtion as in =GETPIVOTDATA($A$2,$A$2,"Rechnungs-Datum",DATE(2010,1,26))
*** Bug 58036 has been marked as a duplicate of this bug. ***
Notes for unit test writers: Revert has to be done manually. Could be a bit complicated as the change touches many locations over three commits. Test could be added to sc/qa/unit/data/functions/spreadsheet/fods/getpivotdata.fods or sc/qa/unit/ucalc_pivottable.cxx