Description: Hi I am using Calc 7.1.1.2 (x64) Windows version of Libre Office. I have a .XLSX file (Excel 2007-365 format) that I created using Libre Office from scratch (not imported from Excel or anything). I have created a simple pivot table in the below format. Month Sales % of Sales Jan 10 20% Feb 15 30% Mar 25 50% Total 50 100% The 2nd and 3rd column in the above table both are sourced from a single column called Sales. The 2nd column has "Sum" function. The 3rd column also has "Sum" function applied, but in the displayed value I have chosen type as "% of Total". The problem is that the "% of Total" type in the Displayed Value setting doesn't persist forever. When I save the spreadsheet, close and again reopen it, the "% of Total" setting is gone. It reverts back to just normal "Sum" settings only. So both the columns above just show the "Sum" only. When I close and reopen it, it shows like below. Month Sales % of Sales Jan 10 10 Feb 15 15 Mar 25 25 Total 50 50 Can you please help fix this issue. I have had this issue through version 6 and version 7 so far. I keep upgrading to latest versions hoping that this would be fixed and it hasn't been so far. Thanks. Steps to Reproduce: 1. Create a new spreadsheet and save it in .XLSX file (Excel 2007-365 format). 2. Type the below values in sheet in 2 columns along with their titles. Month Sales Jan 5 Jan 5 Feb 10 Feb 5 Mar 5 Mar 10 Mar 10 3. Create Pivot Table from the above data. a. The Month should be Row Fields. b. The Sales field should be in Data Fields. c. Select Data field 2 times and drop it in the Data Fields Section. d. Double click on Sales field (1st occurrence) and Choose the Function Sum. Click OK. e. Double click on Sales field (2nd occurrence) and Choose the Function Sum again. d. Expand the Displayed Value Option and Choose "% of Total" from Type Drop Down. Click OK. 3. Save the Spreadsheet and Close it. 4. Reopen it and examine the Pivot Table. Actual Results: Month Sales % of Sales Jan 10 10 Feb 15 15 Mar 25 25 Total 50 50 Expected Results: Month Sales % of Sales Jan 10 20% Feb 15 30% Mar 25 50% Total 50 100% Reproducible: Always User Profile Reset: Yes Additional Info: This problem will happen even if you had only one field and you selected % of Total for that field and XLSX format for saving the file. Choosing the same field twice and displaying Sum for one field and displaying % of Total for the second field is NOT the trigger/prerequisite for this issue. This is simply the % of Total Setting not getting saved/perpetuated and getting lost once closed and then reopened again.
This problem happens only in .XLSX version. The behaviour is normal in .ODS version.
Thank you for reporting the bug. Please attach a sample document, as this makes it easier for us to verify the bug. I have set the bug's status to 'NEEDINFO'. Please change it back to 'UNCONFIRMED' once the requested document is provided. (Please note that the attachment will be public, remove any sensitive information before attaching it. See https://wiki.documentfoundation.org/QA/FAQ#How_can_I_eliminate_confidential_data_from_a_sample_document.3F for help on how to do so.)
Created attachment 170926 [details] Steps to Reproduce the Problem
Created attachment 170927 [details] Sample in XLSX Format
Created attachment 170928 [details] Sample in ODS Format
Reproduced Version: 7.1.4.2 (x64) / LibreOffice Community Build ID: a529a4fab45b75fefc5b6226684193eb000654f6 CPU threads: 4; OS: Windows 10.0 Build 21387; UI render: Skia/Raster; VCL: win Locale: es-ES (es_ES); UI: en-US Calc: threaded It lose the '% of Total' after save as xlsx.
Reproduced in recent trunk build: Version: 25.2.0.0.alpha0+ (X86_64) / LibreOffice Community Build ID: 8705cfecd5a10f817d3a2a02041d85e77282aa30 CPU threads: 8; OS: Linux 6.5; UI render: default; VCL: gtk3 Locale: en-AU (en_AU.UTF-8); UI: en-US Calc: CL threaded Also in 4.4.0.3, but not in 4.3.0.4 -> regression. Bibisected with linux-44max repo to first bad build [61c7a88e60ab9d5079194c16bc0dec0772f9f76d] which is: commit f5aeaa3845ae794a6320f082b1354f946d96cec1 author Kohei Yoshida Fri Aug 22 22:57:05 2014 -0400 committer Kohei Yoshida Thu Aug 28 21:37:08 2014 -0400 bnc#822173: Initial work on exporting pivot cache and pivot table to xlsx. XLSX import of the equivalent MS 365 Excel setting (Value field settings > Show Value as > % of grand total) still works. Kohei, any chance you could have a look?
*** Bug 163238 has been marked as a duplicate of this bug. ***