[Libreoffice-bugs] [Bug 113002] LibreOfficeCalc: Pivot Table recurring data field name will added a simple quotation marks before number

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=113002

--- Comment #23 from Commit Notification 
 ---
Andreas Heinisch committed a patch related to this issue.
It has been pushed to "libreoffice-7-4":

https://git.libreoffice.org/core/commit/b013342bf7b16cb2393bd7e020707ff58f4d88de

tdf#113002 - Add numeric flag to recurring data fields

It will be available in 7.4.0.0.beta2.

The patch should be included in the daily builds available at
https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
https://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.

-- 
You are receiving this mail because:
You are the assignee for the bug.

[Libreoffice-bugs] [Bug 113002] LibreOfficeCalc: Pivot Table recurring data field name will added a simple quotation marks before number

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=113002

Commit Notification  changed:

   What|Removed |Added

 Whiteboard|target:7.5.0|target:7.5.0
   ||target:7.4.0.0.beta2

-- 
You are receiving this mail because:
You are the assignee for the bug.

[Libreoffice-bugs] [Bug 113002] LibreOfficeCalc: Pivot Table recurring data field name will added a simple quotation marks before number

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=113002

Andreas Heinisch  changed:

   What|Removed |Added

   Assignee|andreas.heini...@yahoo.de   |libreoffice-b...@lists.free
   ||desktop.org
 Status|ASSIGNED|NEW

--- Comment #22 from Andreas Heinisch  ---
The other issue is on purpose and no idea, if we should fix it.

-- 
You are receiving this mail because:
You are the assignee for the bug.

[Libreoffice-bugs] [Bug 113002] LibreOfficeCalc: Pivot Table recurring data field name will added a simple quotation marks before number

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=113002

Andreas Heinisch  changed:

   What|Removed |Added

   Assignee|libreoffice-b...@lists.free |andreas.heini...@yahoo.de
   |desktop.org |
 Status|NEW |ASSIGNED

-- 
You are receiving this mail because:
You are the assignee for the bug.

[Libreoffice-bugs] [Bug 113002] LibreOfficeCalc: Pivot Table recurring data field name will added a simple quotation marks before number

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=113002

Robert Lacroix  changed:

   What|Removed |Added

 CC||p.ho...@gmail.com

--- Comment #19 from Robert Lacroix  ---
*** Bug 135827 has been marked as a duplicate of this bug. ***

-- 
You are receiving this mail because:
You are the assignee for the bug.___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

[Libreoffice-bugs] [Bug 113002] LibreOfficeCalc: Pivot Table recurring data field name will added a simple quotation marks before number

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=113002

--- Comment #18 from m.a.riosv  ---
*** Bug 128693 has been marked as a duplicate of this bug. ***

-- 
You are receiving this mail because:
You are the assignee for the bug.___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

[Libreoffice-bugs] [Bug 113002] LibreOfficeCalc: Pivot Table recurring data field name will added a simple quotation marks before number

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=113002

--- Comment #17 from Robert Lacroix  ---
(In reply to Robert Lacroix from comment #16)
correction:
> --or--
> 
> =IF(ISNUMBER(qe_date_field),qe_date_field,DATEVALUE(qe_date_field)+30))
> 
> 
> Long story short, the cell reference needed to work around this bug is both
> complex and fragile (being subject to the whims of source column formatting).

Actually this alternate formula doesn't work at all for quarter ending April
30.
See? Use accounting software.

-- 
You are receiving this mail because:
You are the assignee for the bug.___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

[Libreoffice-bugs] [Bug 113002] LibreOfficeCalc: Pivot Table recurring data field name will added a simple quotation marks before number

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=113002

--- Comment #16 from Robert Lacroix  ---
One more comment about the repeated item label values for dates. The text
strings produced for repeated item labels follow the formatting of the source
column. So dates in the source formatted as "MMM " produce 8-character
values for text item labels, while dates in the source formatted as
"-MM-DD" produce 10-character values for text item labels. Meanwhile the
value of the numeric item labels in the PT retain full date precision
regardless of the formatting of the source column or formatting of the pivot
table cells.

It bears pointing out that under all circumstances, the VALUE of an item label
should reflect the value of the source column (whether it's an original item
label or a repeated item label). The FORMAT of the item label should reflect,
primarily, the format applied to cells in the pivot table, and if otherwise
cleared of formatting, secondarily to the format of the source column. IT IS A
SERIOUS BUG to use the formatted appearance of a source column for the value of
an item label.

TLDR; stop here.

To belabour the point, let's look at an example that would compute COST OF
GOODS SOLD in a business' quarterly income statement, where the fiscal year end
is July 31. (NB: real accounting software should be used instead of LO-Calc,
but we are talking about a hypothetical example; CAN and SHOULD are
irrelevant).

The source data is a list of sales transactions. Among the data columns are:
"tr_date" the transaction date of an invoice
"barcode" of each item sold on an invoice
"quantity" of each item sold on an invoice

A new source column "qe_date" is added which computes the last date of a fiscal
quarter (it's pretty ugly).

=EOMONTH(tr_date,(INT(MOD((MONTH(tr_date)+4),12)/3)+1)*3+7-MONTH(tr_date))

A pivot table is used to tabulate the total number of each item sold by barcode
in every fiscal quarter. The PT fields are
"qe_date_field" the result of selecting "qe_date" as a row field
"barcode_field" the result of selecting "barcode" as a row field
"sum(quantity)" the data field

In the leftmost row postion, the "Repeat item labels" option must be turned on
for "qe_date_field" since we want to use that field to look up the quarterly
average price of inventory in another table for computing COST OF GOODS SOLD
for the quarter. The price lookup could be done in the source table, but let's
follow through this example because there might be only 160 lookups in the PT
compared to doing this in the source table which might be 2000 lookups.

For item labels of "qe_date_field" which are numeric, a simple cell reference
suffices to extract the relevant quarter-ending date.

=qe_date_field

But for text items labels it's a different story. We must use DATEVALUE() to
get the date. But DATEVALUE produces an error for numeric fields, we must test
"qe_date_field" thusly:

=IF(ISNUMBER(qe_date_field),qe_date_field,DATEVALUE(qe_date_field))

This works fine when "-MM-DD" is the date format of the "qe_date" source
column. All PT item labels, including repeated labels, show the correct
quarter-ending date.

But say the source column uses a natural date format for quarter-ending. Using
the date format "MMM ", DATEVALUE(qe_date_field) produces the first day of
the month instead of the last, so the cell reference must be

=IF(ISNUMBER(qe_date_field),qe_date_field,EOMONTH(DATEVALUE(qe_date_field)))

--or--

=IF(ISNUMBER(qe_date_field),qe_date_field,DATEVALUE(qe_date_field)+30))


Long story short, the cell reference needed to work around this bug is both
complex and fragile (being subject to the whims of source column formatting).

-- 
You are receiving this mail because:
You are the assignee for the bug.___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

[Libreoffice-bugs] [Bug 113002] LibreOfficeCalc: Pivot Table recurring data field name will added a simple quotation marks before number

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=113002

--- Comment #15 from Robert Lacroix  ---
e: #(In reply to Robert Lacroix from comment #14)

> One workaround is to convert this column in the source data to a text value.
> One way to do this for computed values is to append an empty string onto the
> computation. For example:  =date(1995,7,28)&""  Then all the item labels
> will be text for that field.
> 
> This is an unsatisfactory workaround if you expect the field to have numeric
> values, like when applying conditional formatting or when using the labels
> in a numeric computation. For instance when cell A3 contains a text value 
> '1234  and cell D3 contains a formula  =A3>1235  the value of cell D3 is
> TRUE.

Actually, this workaround is barely even useful - it works only for numbers
whose integer part is fixed-length, like a bar code. Integers of varying length
converted to text strings will sort ascending lexically so that  "1000" comes
before "104". The workaround sorts the text representation of the date/time
values correctly but the resulting string of digits is practically worthless
because you can't interpret it as a date without converting it back to a
number.

-- 
You are receiving this mail because:
You are the assignee for the bug.___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

[Libreoffice-bugs] [Bug 113002] LibreOfficeCalc: Pivot Table recurring data field name will added a simple quotation marks before number

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=113002

--- Comment #14 from Robert Lacroix  ---
Bug is present.

Version: 6.1.5.2
Build ID: 1:6.1.5-3+deb10u6
CPU threads: 12; OS: Linux 4.19; UI render: default; VCL: x11; 
Locale: en-CA (en_CA.utf8); Calc: group threaded

This happens with any numeric value used as a row field with the field option
"Repeat item labels" enabled. Original labels are numeric and repeated labels
are converted to text.

The problem is not merely cosmetic. Using the labels of this field in the PT as
the SearchCriterion of a VLOOKUP formula will have different results depending
on whether the particular row's item label is numeric or text.

One workaround is to convert this column in the source data to a text value.
One way to do this for computed values is to append an empty string onto the
computation. For example:  =date(1995,7,28)&""  Then all the item labels will
be text for that field.

This is an unsatisfactory workaround if you expect the field to have numeric
values, like when applying conditional formatting or when using the labels in a
numeric computation. For instance when cell A3 contains a text value  '1234 
and cell D3 contains a formula  =A3>1235  the value of cell D3 is TRUE.

-- 
You are receiving this mail because:
You are the assignee for the bug.___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

[Libreoffice-bugs] [Bug 113002] LibreOfficeCalc: Pivot Table recurring data field name will added a simple quotation marks before number

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=113002

Milston B.  changed:

   What|Removed |Added

 CC||mils...@live.de

--- Comment #13 from Milston B.  ---
Same behaviour in Windows 10



 Version: 7.0.1.2 (x86)
 Build ID: 7cbcfc562f6eb6708b5ff7d7397325de9e764452
 CPU threads: 4; OS: Windows 10.0 Build 17763; UI render: Skia/Raster; VCL:
win
 Locale: de-DE (de_DE); UI: de-DE

-- 
You are receiving this mail because:
You are the assignee for the bug.___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

[Libreoffice-bugs] [Bug 113002] LibreOfficeCalc: Pivot Table recurring data field name will added a simple quotation marks before number

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=113002

--- Comment #12 from Jean-Marc Le Peuvédic  ---
This bug is present in version 7no linux.

Version: 7.0.1.2
Build ID: 88a63c56098013eb4038e11ebe7c8c0daab09aa8
CPU threads: 4; OS: Linux 5.4; UI render: default; VCL: gtk3
Locale: fr-FR (fr_FR.UTF-8); Langue IHM : fr-FR
Calc: threaded

The first entry in the pivot table is correct, the following entries have a
quote.

-- 
You are receiving this mail because:
You are the assignee for the bug.___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

[Libreoffice-bugs] [Bug 113002] LibreOfficeCalc: Pivot Table recurring data field name will added a simple quotation marks before number

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=113002

--- Comment #11 from QA Administrators  ---
Dear camisso,

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 with the latest version of LibreOffice
from https://www.libreoffice.org/download/

If the bug is present, please leave a comment that includes the information
from Help - About LibreOffice.

If the bug is NOT present, please set the bug's Status field to
RESOLVED-WORKSFORME and leave a comment that includes the information from Help
- About LibreOffice.

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)


If you want to do more to help you can test to see if your issue is a
REGRESSION. To do so:
1. Download and install oldest version of LibreOffice (usually 3.3 unless your
bug pertains to a feature added after 3.3) from
http://downloadarchive.documentfoundation.org/libreoffice/old/

2. Test your bug
3. Leave a comment with your results.
4a. If the bug was present with 3.3 - set version to 'inherited from OOo';
4b. If the bug was not present in 3.3 - add 'regression' to keyword


Feel free to come ask questions or to say hello in our QA chat:
https://kiwiirc.com/nextclient/irc.freenode.net/#libreoffice-qa

Thank you for helping us make LibreOffice even better for everyone!

Warm Regards,
QA Team

MassPing-UntouchedBug

-- 
You are receiving this mail because:
You are the assignee for the bug.___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

[Libreoffice-bugs] [Bug 113002] LibreOfficeCalc: Pivot Table recurring data field name will added a simple quotation marks before number

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=113002

--- Comment #10 from cami...@hotmail.com ---
The bug is still present.
Version: 6.1.2.1 (x64)

-- 
You are receiving this mail because:
You are the assignee for the bug.___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

[Libreoffice-bugs] [Bug 113002] LibreOfficeCalc: Pivot Table recurring data field name will added a simple quotation marks before number

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=113002

--- Comment #9 from QA Administrators  ---
** 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 with the latest version of LibreOffice
from https://www.libreoffice.org/download/

If the bug is present, please leave a comment that includes the information
from Help - About LibreOffice.

If the bug is NOT present, please set the bug's Status field to
RESOLVED-WORKSFORME and leave a comment that includes the information from Help
- About LibreOffice.

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)


If you want to do more to help you can test to see if your issue is a
REGRESSION. To do so:
1. Download and install oldest version of LibreOffice (usually 3.3 unless your
bug pertains to a feature added after 3.3) from
http://downloadarchive.documentfoundation.org/libreoffice/old/

2. Test your bug
3. Leave a comment with your results.
4a. If the bug was present with 3.3 - set version to 'inherited from OOo';
4b. If the bug was not present in 3.3 - add 'regression' to keyword


Feel free to come ask questions or to say hello in our QA chat:
https://kiwiirc.com/nextclient/irc.freenode.net/#libreoffice-qa

Thank you for helping us make LibreOffice even better for everyone!

Warm Regards,
QA Team

MassPing-UntouchedBug

-- 
You are receiving this mail because:
You are the assignee for the bug.___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

[Libreoffice-bugs] [Bug 113002] LibreOfficeCalc: Pivot Table recurring data field name will added a simple quotation marks before number

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=113002

m.a.riosv  changed:

   What|Removed |Added

 Status|UNCONFIRMED |NEW
 Ever confirmed|0   |1

--- Comment #8 from m.a.riosv  ---
Ok, I see that the first time a year appears on the list it's as number without
the quotation mark while the rest have it.

-- 
You are receiving this mail because:
You are the assignee for the bug.___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

[Libreoffice-bugs] [Bug 113002] LibreOfficeCalc: Pivot Table recurring data field name will added a simple quotation marks before number

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=113002

--- Comment #7 from cami...@hotmail.com ---
@m.a.riosv
Did you look at the end of the table? There are, even after refesh, the numbers
of the Year, above are year numbers as string.

-- 
You are receiving this mail because:
You are the assignee for the bug.___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

[Libreoffice-bugs] [Bug 113002] LibreOfficeCalc: Pivot Table recurring data field name will added a simple quotation marks before number

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=113002

--- Comment #6 from m.a.riosv  ---
After refresh sort it's fine for me. To avoid lost the CF give it a range
larger than PT rows because format of PT cells is reset with refresh.

-- 
You are receiving this mail because:
You are the assignee for the bug.___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

[Libreoffice-bugs] [Bug 113002] LibreOfficeCalc: Pivot Table recurring data field name will added a simple quotation marks before number

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=113002

--- Comment #5 from cami...@hotmail.com ---
Created attachment 136900
  --> https://bugs.documentfoundation.org/attachment.cgi?id=136900=edit
Example for mismatch, if newest are sorted and number as row, sorting is wrong
for two columns

-- 
You are receiving this mail because:
You are the assignee for the bug.___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

[Libreoffice-bugs] [Bug 113002] LibreOfficeCalc: Pivot Table recurring data field name will added a simple quotation marks before number

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=113002

--- Comment #4 from m.a.riosv  ---
I think the sort on the PT it's done fine. And for CF should not be difficult
get it working, please if you are not able, ask the question on
https://ask.libreoffice.org/en/questions/

-- 
You are receiving this mail because:
You are the assignee for the bug.___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

[Libreoffice-bugs] [Bug 113002] LibreOfficeCalc: Pivot Table recurring data field name will added a simple quotation marks before number

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=113002

m.a.riosv  changed:

   What|Removed |Added

 Status|NEEDINFO|UNCONFIRMED
 Ever confirmed|1   |0

--- Comment #3 from m.a.riosv  ---
Yes the year has a quotation mark, but if I'm not wrong dates a showed as text
on the pivot table, e.g. GETPIVOTDATA() function search for text to find the
data.

Maybe some light on this bug report
https://bugs.documentfoundation.org/show_bug.cgi?id=35247

-- 
You are receiving this mail because:
You are the assignee for the bug.___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

[Libreoffice-bugs] [Bug 113002] LibreOfficeCalc: Pivot Table recurring data field name will added a simple quotation marks before number

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=113002

--- Comment #2 from cami...@hotmail.com ---
Created attachment 136898
  --> https://bugs.documentfoundation.org/attachment.cgi?id=136898=edit
Example for the pivot table with recurring names

-- 
You are receiving this mail because:
You are the assignee for the bug.___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

[Libreoffice-bugs] [Bug 113002] LibreOfficeCalc: Pivot Table recurring data field name will added a simple quotation marks before number

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=113002

m.a.riosv  changed:

   What|Removed |Added

 Status|UNCONFIRMED |NEEDINFO
 CC||miguelangelrv@libreoffice.o
   ||rg
 Ever confirmed|0   |1

--- Comment #1 from m.a.riosv  ---
(In reply to camisso from comment #0)
>
> If I have already numbers, e.g. year, and I check at data field options
> recurring names there will be a mismatch, the original numbers are numbers
> and the recurring names are strings. No correct sorting is possible and
> Conditional Formatting will work only for the numbers, not the recurring
> names.

Please attach a sample file.

-- 
You are receiving this mail because:
You are the assignee for the bug.___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

[Libreoffice-bugs] [Bug 113002] LibreOfficeCalc: Pivot Table recurring data field name will added a simple quotation marks before number

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=113002

Pénzes Dávid  changed:

   What|Removed |Added

 Blocks||103381


Referenced Bugs:

https://bugs.documentfoundation.org/show_bug.cgi?id=103381
[Bug 103381] [META] Pivot table (aka Data Pilot) bugs and enhancements
-- 
You are receiving this mail because:
You are the assignee for the bug.___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs