Bug 157016 - Calc can't set different values for 'overlap' and'spacing' parameters for primary and secondary axes in the 'data series' options
Summary: Calc can't set different values for 'overlap' and'spacing' parameters for pri...
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
3.4.0 release
Hardware: All All
: medium normal
Assignee: Not Assigned
URL: https://ask.libreoffice.org/t/106017/
Whiteboard:
Keywords: filter:xlsx
Depends on:
Blocks: OOXML-Chart
  Show dependency treegraph
 
Reported: 2023-08-30 16:09 UTC by Piotr Osada
Modified: 2024-05-20 17:27 UTC (History)
2 users (show)

See Also:
Crash report or crash signature:


Attachments
kopia.xlsx (9.53 KB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2023-08-30 16:11 UTC, Piotr Osada
Details
kopia.ods (25.27 KB, application/vnd.oasis.opendocument.spreadsheet)
2023-08-30 16:11 UTC, Piotr Osada
Details
Data series options (20.02 KB, image/png)
2023-08-30 16:11 UTC, Piotr Osada
Details
Intended chart (62.37 KB, image/png)
2023-08-30 16:13 UTC, Piotr Osada
Details
Broken xlsx in Calc (57.06 KB, image/png)
2023-08-30 16:14 UTC, Piotr Osada
Details
Broken xlsx in Excel (49.00 KB, image/png)
2023-08-30 16:14 UTC, Piotr Osada
Details
ODS - Proper chart in Calc (57.33 KB, image/png)
2023-08-30 16:15 UTC, Piotr Osada
Details
ODS - Broken chart in Excel (60.64 KB, image/png)
2023-08-30 16:16 UTC, Piotr Osada
Details
Data series options (general view) (62.83 KB, image/png)
2023-08-30 16:18 UTC, Piotr Osada
Details
MS Office info icon by the options checkboxes with description (53.39 KB, image/png)
2023-08-31 12:16 UTC, Piotr Osada
Details
Excel overlap spacing parameters.png (299.83 KB, image/png)
2023-08-31 14:28 UTC, Piotr Osada
Details
Overlap,Spacing -100,200 & -100,400.ods (22.79 KB, application/vnd.oasis.opendocument.spreadsheet)
2023-08-31 14:31 UTC, Piotr Osada
Details
Calc can't assign different overlap,spacing parameters to different axis series.png (170.04 KB, image/png)
2023-08-31 14:34 UTC, Piotr Osada
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Piotr Osada 2023-08-30 16:09:18 UTC
Version: 7.6.0.3 (X86_64) / LibreOffice Community
Build ID: 69edd8b8ebc41d00b4de3915dc82f8f0fc3b6265
CPU threads: 8; OS: Windows 10.0 Build 22621; UI render: Skia/Raster; VCL: win
Locale: pl-PL (pl_PL); UI: en-US
Calc: threaded

When we put 'Show bars side by side' in a.xlsx file and close it, the option won't remember. It doesn't matter if we open the file again in Calc or Excel.

This does not happen with .ods file in Calc, but does happen in Excel.
Comment 1 Piotr Osada 2023-08-30 16:11:08 UTC
Created attachment 189253 [details]
kopia.xlsx
Comment 2 Piotr Osada 2023-08-30 16:11:22 UTC
Created attachment 189254 [details]
kopia.ods
Comment 3 Piotr Osada 2023-08-30 16:11:50 UTC
Created attachment 189255 [details]
Data series options
Comment 4 Piotr Osada 2023-08-30 16:13:43 UTC
Created attachment 189256 [details]
Intended chart
Comment 5 Piotr Osada 2023-08-30 16:14:15 UTC
Created attachment 189257 [details]
Broken xlsx in Calc
Comment 6 Piotr Osada 2023-08-30 16:14:32 UTC
Created attachment 189258 [details]
Broken xlsx in Excel
Comment 7 Piotr Osada 2023-08-30 16:15:49 UTC
Created attachment 189259 [details]
ODS - Proper chart in Calc
Comment 8 Piotr Osada 2023-08-30 16:16:22 UTC
Created attachment 189260 [details]
ODS - Broken chart in Excel
Comment 9 Piotr Osada 2023-08-30 16:18:35 UTC
Created attachment 189261 [details]
Data series options (general view)
Comment 10 m_a_riosv 2023-08-30 21:55:14 UTC
Is the option available in Excel?
Comment 11 Piotr Osada 2023-08-31 07:23:04 UTC
The same result in:

Version: 7.5.2.2 (X86_64) / LibreOffice Community
Build ID: 53bb9681a964705cf672590721dbc85eb4d0c3a2
CPU threads: 8; OS: Linux 6.2; UI render: default; VCL: gtk3
Locale: pl-PL (pl_PL.UTF-8); UI: en-US
Flatpak
Calc: threaded
Comment 12 Piotr Osada 2023-08-31 07:39:35 UTC
(In reply to m.a.riosv from comment #10)
> Is the option available in Excel?

It look's like there is no similar option.

I have never seen setting like this in Excel and as of my research there are only 'Series Overlap' and 'Gap Width' sliders in user interface:

https://www.exceldashboardtemplates.com/stop-excel-from-overlapping-the-columns-when-moving-a-data-series-to-the-second-axis/

https://www.youtube.com/watch?v=Nja4FJ7gIEg

https://www.youtube.com/watch?v=O73_ve-Lv5w

https://www.google.com/search?q=how+to+separate+bars+in+excel+bar+chart&sca_esv=561558033&hl=pl&ei=WkHwZOP7E7_PwPAP57eryAs&ved=0ahUKEwjjkNKJsIaBAxW_JxAIHefbCrkQ4dUDCA4&uact=5&oq=how+to+separate+bars+in+excel+bar+chart&gs_lp=Egxnd3Mtd2l6LXNlcnAiJ2hvdyB0byBzZXBhcmF0ZSBiYXJzIGluIGV4Y2VsIGJhciBjaGFydDIHEAAYExiABDIIEAAYFhgeGBMyCBAAGBYYHhgTSOVZUIoMWM1YcAJ4AZABAJgBmQKgAfEdqgEGMzEuOC4xuAEDyAEA-AEBwgIKEAAYRxjWBBiwA8ICERAuGIAEGLEDGIMBGMcBGNEDwgILEAAYgAQYsQMYgwHCAgsQLhiDARixAxiKBcICDhAuGIAEGLEDGMcBGNEDwgIgEC4YgAQYsQMYgwEYxwEY0QMYlwUY3AQY3gQY4ATYAQHCAgsQLhiABBixAxiDAcICCxAAGIoFGLEDGIMBwgIXEC4YgAQYsQMYgwEYxwEYrwEYmAUYmQXCAgUQLhiABMICCBAAGIAEGLEDwgIFEAAYgATCAhQQLhiABBiXBRjcBBjeBBjgBNgBAcICCBAuGIAEGLEDwgIJEAAYDRgTGIAEwgIGEAAYFhge4gMEGAAgQYgGAZAGBLoGBggBEAEYFA&sclient=gws-wiz-serp

https://www.google.com/search?q=how+to+set+chart+in+excel+not+to+overlap+bars&sca_esv=561558033&hl=pl&ei=KEHwZMS5MdWXjgb8066IBg&ved=0ahUKEwjE7YPyr4aBAxXVi8MKHfypC2EQ4dUDCGo&uact=5&oq=how+to+set+chart+in+excel+not+to+overlap+bars&gs_lp=Egxnd3Mtd2l6LXNlcnAiLWhvdyB0byBzZXQgY2hhcnQgaW4gZXhjZWwgbm90IHRvIG92ZXJsYXAgYmFyczIFECEYoAEyCBAhGBYYHhgdMggQIRgWGB4YHUjDkwFQpR1Y0ZEBcAZ4AZABAJgBeqABgxKqAQQxNS45uAEDyAEA-AEBwgIKEAAYRxjWBBiwA8ICCBAhGKABGMMEwgIFEAAYogTCAgQQIRgKwgIEECEYFeIDBBgAIEGIBgGQBgg&sclient=gws-wiz-serp
Comment 13 m_a_riosv 2023-08-31 11:24:50 UTC
If the option, it's not in Excel, that explain the reason for what it is not saved in xlsx format.
Comment 14 Piotr Osada 2023-08-31 12:14:29 UTC
(In reply to m.a.riosv from comment #13)
> If the option, it's not in Excel, that explain the reason for what it is not
> saved in xlsx format.

Then what about providing information in the user interface, that this option is incompatible with .xlsx format in such kind of cases? This could save some/a lot of time of users struggling with this effect.
Comment 15 Piotr Osada 2023-08-31 12:16:53 UTC
Created attachment 189289 [details]
MS Office info icon by the options checkboxes with description

I meant these kinds of additional informations.
Comment 16 Piotr Osada 2023-08-31 14:28:31 UTC
Created attachment 189294 [details]
Excel overlap spacing parameters.png

Different approach to achieve a similar chart:
adjusting overlap and spacing parameters.
Comment 17 Piotr Osada 2023-08-31 14:31:02 UTC
Created attachment 189295 [details]
Overlap,Spacing -100,200 & -100,400.ods
Comment 18 Piotr Osada 2023-08-31 14:34:42 UTC
Created attachment 189296 [details]
Calc can't assign different overlap,spacing parameters to different axis series.png

It looks like Calc does not able to assign 'overlap', 'spacing' parameters separately to primary and secondary axis to XLSX file. In ODS everything works fine.
Comment 19 Piotr Osada 2023-08-31 14:40:08 UTC
(In reply to Piotr Osada from comment #17)
> Created attachment 189295 [details]
> Overlap,Spacing -100,200 & -100,400.ods

Version: 7.6.0.3 (X86_64) / LibreOffice Community
Build ID: 69edd8b8ebc41d00b4de3915dc82f8f0fc3b6265
CPU threads: 8; OS: Windows 10.0 Build 22621; UI render: Skia/Raster; VCL: win
Locale: pl-PL (pl_PL); UI: en-US
Calc: threaded
Comment 20 m_a_riosv 2023-08-31 15:20:25 UTC
Let's see if someone else with better acknowledge on charts, can help here.
Comment 21 Stéphane Guillou (stragu) 2023-09-29 22:19:00 UTC
(In reply to Piotr Osada from comment #18)
> Created attachment 189296 [details]
> Calc can't assign different overlap,spacing parameters to different axis
> series.png
> 
> It looks like Calc does not able to assign 'overlap', 'spacing' parameters
> separately to primary and secondary axis to XLSX file. In ODS everything
> works fine.

From your attachment 189294 [details], I understand that MS Office is able to keep those overlap/spacing settings as they are when saving as XLSX?

I can at least confirm the behaviour when saving as XLSX from LibreOffice: the two data series get the same Overlap/Spacing values.

Version: 7.6.2.1 (X86_64) / LibreOffice Community
Build ID: 56f7684011345957bbf33a7ee678afaf4d2ba333
CPU threads: 8; OS: Linux 5.15; UI render: default; VCL: gtk3
Locale: en-AU (en_AU.UTF-8); UI: en-US
Calc: threaded

And in previous versions, it would mess up the chart in various ways. The values were already reset to equal values between series back in 3.4.0rc1, and the current look started in 7.2.
Comment 22 Piotr Osada 2023-10-03 05:59:45 UTC
(In reply to Stéphane Guillou (stragu) from comment #21)
> From your attachment 189294 [details], I understand that MS Office is able
> to keep those overlap/spacing settings as they are when saving as XLSX?

Right, MS Office keeps those settings for both axes separately in XLSX.