Created attachment 66809 [details] pivot_table_bug Open attachment, refresh pivot table at the bottom of the table. Starting from 3.6.x Libre Office Calc stopped displaying correctly time column and instead of having time summed I have regular numbers/fractions. Same spreadsheet works ok in the previous versions of LO as well as refreshing document produced in 3.6 returns to the proper form.
Calculations are well-performed. Displaying the time format is defective.
Verified. Since you said it used to work I'm going to add Regression to whiteboard. Leaving as normal because of the regression (otherwise I'd say minor). Marking as HIGH as it's a regression.
Created attachment 68641 [details] bibisect36a
Just tested it with the latest 3.6.4 and the bug is still there. I thought that it might be fixed by the https://bugs.freedesktop.org/show_bug.cgi?id=54898 but it wasn't.
regression is a keyword...
PIVOTTABLE has its own BSA keyword.
Just tested with the latest 4.0RC2 and the bug is still present.
Version is the oldest version that we can confirm the problem, not the latest it's been tested on. We use comments to say "we tested it on X.X.X.X and it's still present
OK, mea culpa. As expected same is happening in 4.0.0 final. I've been playing with it for a bit and it seems that the issue is related to the output of the function being /empty/ - i.e. double quotes (to have blank field - it seems that then Cals ignores source formatting and assumes the input/output to be plain numeric/text determining pivot table formatting) and also from the fact that (as it seems) formatting of the whole pivot table is taken from last row of the input table selection. I'm going to attach simple testcase sheet. In that testcase if you remove start/end time from last row and refresh pivot table then the issue manifest itself. for the moment I've "fixed" my initial sheet by replacing conditional function: =IF(F278-E278>0;F278-E278;"") with simple /0/ (also, using =IF(F278-E278>0;F278-E278;0) would work) as then times are displayed on in whole pivot table.
Created attachment 74353 [details] formatting of the pivot table is determined by the last row content removing start/end from last row thus causing IF function to output blank (i.e. double quote - "") causes pivot table to assume that the whole column is text forcing different formatting
(In reply to comment #10) > Created attachment 74353 [details] > formatting of the pivot table is determined by the last row content > > removing start/end from last row thus causing IF function to output blank > (i.e. double quote - "") causes pivot table to assume that the whole column > is text forcing different formatting Very good observation. This narrows it down significantly.
The difficulty is how to determine the "representative" number format for a column when the column contains a mixture of number formats. Counting solves it of course, but that would affect performance a bit if done incorrectly.
I think the old code took the number format of the first non-empty cell, while the new code takes the last number format. Either way is not perfect.
OK, I think that taking first item format makes more sense than last one. Second think - can it take format set for the given cell - first (or last, tho not too fond of that) and not the format derived by Calc? For example I format whole column as "time" ([hh]:mm - see examples) but for some cells I don't want to display anything thus I use =IF(cond;val;"") which ten puts empty string (i.e. >> "" << ) into cell and for no apparent reason Calc suddenly treats the cell as string/text and not as 'time' as set in formatting.
Kohei Yoshida committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=dd1b3ca024a85597b46778a62007bb966174368d fdo#54651: Only pick non-default number format for pivot field. 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.
(In reply to comment #14) > OK, I think that taking first item format makes more sense than last one. > Second think - can it take format set for the given cell - first (or last, > tho not too fond of that) and not the format derived by Calc? For example I > format whole column as "time" ([hh]:mm - see examples) but for some cells I > don't want to display anything thus I use =IF(cond;val;"") which ten puts > empty string (i.e. >> "" << ) into cell and for no apparent reason Calc > suddenly treats the cell as string/text and not as 'time' as set in > formatting. The fix I just pushed should take care of that. If not, since that's a separate issue, please file a new bug. FYI I've proposed a backport of my fix into the 4.0 branch as well.
Kohei Yoshida committed a patch related to this issue. It has been pushed to "libreoffice-4-0": http://cgit.freedesktop.org/libreoffice/core/commit/?id=a7223551f8d3c6d08fcfe951ac21ec8dc8ee00af&h=libreoffice-4-0 fdo#54651: Only pick non-default number format for pivot field. It will be available in LibreOffice 4.0.2. 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.
The fix is in the 4.0 branch. I'll call it fixed.
bibisect shows 32b61ed8931acd97e488bc73486244c385a3a974 to be bad, which was on 2012-03-08 long before 3.6 branchoff, thus not a 3.6.0->3.6.x minor release regression and the bug was at least present as early as 3.6.beta.
Markus Mohrhard committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=4725fcd8ea50d565dc443a6cd90011e9dbb6f1b5 OOXML export validation fixes, related fdo#54651 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.