Bug 35247 - PIVOTTABLE: GETPIVOTDATA - Very touchy / inconsistent about Date Formats
Summary: PIVOTTABLE: GETPIVOTDATA - Very touchy / inconsistent about Date Formats
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
3.3.1 release
Hardware: All All
: medium normal
Assignee: Eike Rathke
URL:
Whiteboard: target:5.3.0 unitTestNotes:14
Keywords:
: 58036 (view as bug list)
Depends on:
Blocks:
 
Reported: 2011-03-12 04:19 UTC by famo
Modified: 2023-10-18 15:51 UTC (History)
4 users (show)

See Also:
Crash report or crash signature:


Attachments
Issue_GETPIVOTDATA_date (18.99 KB, application/vnd.oasis.opendocument.spreadsheet)
2011-03-12 04:19 UTC, famo
Details

Note You need to log in before you can comment on or make changes to this bug.
Description famo 2011-03-12 04:19:46 UTC
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.
Comment 1 famo 2011-03-12 04:30:26 UTC
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.
Comment 2 Kohei Yoshida 2011-03-21 19:43:01 UTC
I guess this is my area.
Comment 3 famo 2013-01-30 10:39:47 UTC
@kohei
Why did you removed yourself from the assignee, is this no longer your area?
Comment 4 Kohei Yoshida 2013-01-30 12:47:55 UTC
(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
Comment 5 QA Administrators 2015-02-19 15:49:20 UTC Comment hidden (obsolete)
Comment 6 Buovjaga 2015-03-08 17:18:53 UTC
Confirmed.
F13, F29 and F30 display #REF!

Win 7 Pro 64-bit, LibO Version: 4.4.1.2
Build ID: 45e2de17089c24a1fa810c8f975a7171ba4cd432
Locale: fi_FI
Comment 7 Eike Rathke 2016-06-01 16:22:34 UTC
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..
Comment 8 Commit Notification 2016-06-06 20:22:52 UTC
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.
Comment 9 Eike Rathke 2016-06-07 09:28:20 UTC
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]").
Comment 10 Commit Notification 2016-06-07 14:17:34 UTC
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.
Comment 11 Commit Notification 2016-06-08 10:14:21 UTC
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.
Comment 12 Eike Rathke 2016-06-08 10:48:57 UTC
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))
Comment 13 Eike Rathke 2016-06-17 17:06:55 UTC
*** Bug 58036 has been marked as a duplicate of this bug. ***
Comment 14 Buovjaga 2023-10-18 15:51:05 UTC
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