Bug 141410 - Calc Pivot Table "% of Total" Display Value Setting not getting saved permanently for .XLSX format files
Summary: Calc Pivot Table "% of Total" Display Value Setting not getting saved permane...
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
4.4.0.3 release
Hardware: All All
: medium normal
Assignee: Not Assigned
URL: https://ask.libreoffice.org/en/questi...
Whiteboard:
Keywords: bibisected, bisected, filter:xlsx, regression
: 163238 (view as bug list)
Depends on:
Blocks: Pivot-Table-XLSX
  Show dependency treegraph
 
Reported: 2021-03-31 22:02 UTC by rsreeny
Modified: 2024-10-02 16:12 UTC (History)
6 users (show)

See Also:
Crash report or crash signature:


Attachments
Steps to Reproduce the Problem (87.43 KB, application/pdf)
2021-04-02 22:25 UTC, rsreeny
Details
Sample in XLSX Format (8.48 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2021-04-02 22:26 UTC, rsreeny
Details
Sample in ODS Format (12.73 KB, application/vnd.oasis.opendocument.spreadsheet)
2021-04-02 22:27 UTC, rsreeny
Details

Note You need to log in before you can comment on or make changes to this bug.
Description rsreeny 2021-03-31 22:02:08 UTC
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.
Comment 1 rsreeny 2021-03-31 22:06:24 UTC
This problem happens only in .XLSX version.
The behaviour is normal in .ODS version.
Comment 2 Xisco Faulí 2021-04-01 07:52:38 UTC
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.)
Comment 3 rsreeny 2021-04-02 22:25:20 UTC
Created attachment 170926 [details]
Steps to Reproduce the Problem
Comment 4 rsreeny 2021-04-02 22:26:33 UTC
Created attachment 170927 [details]
Sample in XLSX Format
Comment 5 rsreeny 2021-04-02 22:27:14 UTC
Created attachment 170928 [details]
Sample in ODS Format
Comment 6 m_a_riosv 2021-06-11 12:12:47 UTC
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.
Comment 7 Stéphane Guillou (stragu) 2024-07-22 12:27:34 UTC
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?
Comment 8 m_a_riosv 2024-10-02 16:12:55 UTC
*** Bug 163238 has been marked as a duplicate of this bug. ***