Hello, e.g. for a given table: Date Number 12.01.2008 1 15.01.2008 2 17.01.2008 1 28.01.2008 1 31.01.2008 17 14.02.2008 1 18.02.2008 1 28.02.2008 2 29.02.2008 1 09.03.2008 1 14.03.2008 1 31.03.2008 4 15.04.2008 1 17.04.2008 1 If I make a pivot table of this data, grouped by year and month, the year will added a simple quotation marks before the number. 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. Thank you in advance for your help.
(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.
Created attachment 136898 [details] Example for the pivot table with recurring names
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
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/
Created attachment 136900 [details] Example for mismatch, if newest are sorted and number as row, sorting is wrong for two columns
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.
@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.
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.
** 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
The bug is still present. Version: 6.1.2.1 (x64)
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
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.
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
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.
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.
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 YYYY" produce 8-character values for text item labels, while dates in the source formatted as "YYYY-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 "YYYY-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 YYYY", 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).
(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.
*** Bug 128693 has been marked as a duplicate of this bug. ***
*** Bug 135827 has been marked as a duplicate of this bug. ***
Proposed patch still does not fix: If I make a pivot table of this data, grouped by year and month, the year will added a simple quotation marks before the number. Proposed patch fixes: https://gerrit.libreoffice.org/c/core/+/135915 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.
Andreas Heinisch committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/30529311ffbbe193765aff0ff353d3409fce3a3e tdf#113002 - Add numeric flag to recurring data fields It will be available in 7.5.0. 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.
The other issue is on purpose and no idea, if we should fix it.
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.
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 https://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://web.libera.chat/?settings=#libreoffice-qa Thank you for helping us make LibreOffice even better for everyone! Warm Regards, QA Team MassPing-UntouchedBug