[Libreoffice-bugs] [Bug 58036] New: EDITING: GETPIVOTDATA function only works with dates fields as text.

[b***@freedesktop.org]

https://bugs.freedesktop.org/show_bug.cgi?id=58036

Priority: medium
Bug ID: 58036
Assignee: libreoffice-***@lists.freedesktop.org
Summary: EDITING: GETPIVOTDATA function only works with dates
fields as text.
Severity: normal
Classification: Unclassified
OS: All
Reporter: ***@miguelangel.mobi
Hardware: Other
Status: UNCONFIRMED
Version: 4.0.0.0.beta1
Component: Spreadsheet
Product: LibreOffice

Created attachment 71222
--> https://bugs.freedesktop.org/attachment.cgi?id=71222&action=edit
Sample pivot table with GETPIVOTDATA() function

Win7x64 Ultimate
LODev Version 4.0.0.0.beta1+ (Build ID:
465d3512cca457c79e6ef85ad1cbf6bcc88e888)

Two tables with a pivot table every one.
Same data only change the format of date column.

First table:
GETPIVOTDATA in B13 does not work, looking for a reference date in the pivot
table.
GETPIVOTDATA in B14 works using a date format with TEXT() function in A14.

Second table (only change the format of date column):
GETPIVOTDATA in B29 works looking for a reference date in the pivot table.

Seems that only dates as text or dates with a format with text works as
expected.

[b***@freedesktop.org]

https://bugs.freedesktop.org/show_bug.cgi?id=58036

Joel Madero <***@gmail.com> changed:

What |Removed |Added
----------------------------------------------------------------------------
Status|UNCONFIRMED |NEW
Severity|normal |minor
Priority|medium |lowest
CC| |***@gmail.com
Version|4.0.0.0.beta1 |3.6.3.2 release
Ever confirmed|0 |1

--- Comment #1 from Joel Madero <***@gmail.com> ---
I can confirm this although my skills with getpivotdata are limited.

Marking:

New (Confirmed)

Version (3.6.3.2) - this is oldest confirmed version, I'm sure it has been like
this forever but I can't test back before this version

Minor (I guess in very specific situations you might be prevented from making
high quality work but in 99.9% of the cases this just makes it a little
annoying to get results)

Lowest (Pivot tables in general aren't that common, getpivotdata is even less
common, coupled with the fact that there is a workaround (change data format),
makes this lowest priority.


Thanks for reporting, we'll see what we can do about this :)

[b***@freedesktop.org]

https://bugs.freedesktop.org/show_bug.cgi?id=58036

--- Comment #2 from mariosv <***@miguelangel.mobi> ---
Hi Joel,

the workaround is there, but:

- No where you can know about this limitation, so I have spent much time to
know how to do it. If it is no possible to solve for 4.x release, maybe we need
to mention on the help of the function.

- Breaks the share between different languages, because you need to use the
TEXT(date;format) function with dates, and the second parameter of the function
is not updated as the number cell format, when one use other local settings. Or
combine several function to get the text.

- The GETPIVOTDATA() function, is very powerful, even of course need understand
how it works, but if you can not make it works without know or understand why,
less people are going to get the benefit of use it.

- And will be more easy, when IFERROR() function can be applied.

- For me the more important is that I can organize the data without worried
about the Pivot table format, and using cell references inside the function to
set the field and the conditions.

- But I fear that the problem is not with the function but with the way how the
data are in the Pivot table, so not easy to solve.

- Or it was developed to work so. Then not a bug and the workaround is the
right way. Who knows?

- As I can see in ML Ask Forums, many people use calc as a little database or
to retrieve data from a database, and Pivot table with a couple of enhancements
would be one of the more important tools in calc.

[b***@freedesktop.org]

https://bugs.freedesktop.org/show_bug.cgi?id=58036

--- Comment #3 from Joel Madero <***@gmail.com> ---
Thanks for the updated information, you are right in that I don't know if this
is how it was built or what, I do suspect that it's not a really simple fix.

As for priority being lowest, I'm sure that you know that you are in the top
10% of users just with the fact that you know about pivot tables and how to use
functions like getpivotdata. Just because it is lowest priority doesn't mean
that it won't get fixed, many things go into the order of how bugs/enhancements
are taken care of, only one of which is priority. Other factors can be if a
developer sees the bug confirmed and knows the code well already, or if a
developer just thinks that they want to take it on just because.

I use pivot tables quite frequently but am aware that this isn't the case for
the vast amount of users and because of this, it should be treated as a low
priority compared to a bug like this:
https://bugs.freedesktop.org/show_bug.cgi?id=34423 where a lot of users insert
images and want to play around with rotation.

Thanks again for the update, don't think we'll ignore this one just because it
has a "lowest" priority :)

[unknown]

https://bugs.freedesktop.org/show_bug.cgi?id=58036

m.a.riosv <***@miguelangel.mobi> changed:

What |Removed |Added
----------------------------------------------------------------------------

[b***@freedesktop.org]

https://bugs.freedesktop.org/show_bug.cgi?id=58036

--- Comment #5 from Alex Thurgood <***@yahoo.co.uk> ---
Adding self to CC if not already on