Bug 115513 - Chart: Checkbox for zero values to be handled like missing values in plot options
Summary: Chart: Checkbox for zero values to be handled like missing values in plot opt...
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Chart (show other bugs)
(earliest affected)
Hardware: All All
: medium normal
Assignee: Not Assigned
Depends on:
Blocks: Chart-Enhancements
  Show dependency treegraph
Reported: 2018-02-07 12:03 UTC by Thomas Lendo
Modified: 2018-04-04 18:10 UTC (History)
5 users (show)

See Also:
Crash report or crash signature:

Calc example file showing 0 values (22.99 KB, application/vnd.oasis.opendocument.spreadsheet)
2018-02-07 12:03 UTC, Thomas Lendo

Note You need to log in before you can comment on or make changes to this bug.
Description Thomas Lendo 2018-02-07 12:03:03 UTC
Created attachment 139660 [details]
Calc example file showing 0 values

When editing data series in a Chart, the Options dialog shows "Plot Options" with that the presentation of missing values can be configured.

I suggest a checkbox for zero (0) values so that they will be handled like missing values.

The reason for this request is that I've spreadsheets with several calculated sums for every year. For example a spreadsheet with data of sold spare parts per year with own columns for the sum of orders received, invoices, sent quotations, estimated net profit, etc.
Some sums of the actual year like "sent quotations" can't be finished during the year. The full information is only available at the end of the year. But I want to show _not_ zero (0) quotations for this year in the chart. I want that the chart line ends in the year before so that there is no line in this year for "sent quotations" if in "plot missing values" the option "Leave gap" is choosen.

Also 1) I don't want to edit the data range (what is my workaround now) so that the actual year won't be handled in the chart for this column or 2) I don't want to edit the formula. The formula and chart should work automatically without manually "correction" which is the cause for mistakes.

Proposed Data Series Options dialog, Plot Options:

Plot missing values  (x) Leave gap
                     ( ) Assume zero
                     ( ) Continue line
[ ] Include values from hidden cells
[ ] Handle zero (0) like missing values           <--- new checkbox

Maybe "Assume zero" should be greyed out when activating the new checkbox because together it doesn't make sense.

In my attached example file I want this option for the green and dark blue and light blue lines. The lines should end in 2017 because now it's misleading information. (The yellow and red bar will be filled with data every month, so they are showing a continual information level.)
Comment 1 Heiko Tietze 2018-02-27 08:56:25 UTC
Not convinced. What happens with tupels containing zero, like x=0,y=42?

Weird but implemented solution for zero values is to hide them via Tools > Options > Calc > View, and that could be respected for charts. There was a ticket recently to show/hide zeros for printing.
Comment 2 Regina Henschel 2018-02-27 12:50:12 UTC
If the data is 0, then it is correct to use this 0. The error in this case is, that the chart is not based on the original data, but on a copy, which has a 0 instead of the blank cell.

To solve such problems, the user can use the original data or make a copy, which indicates, that the original data does not exist, e.g.

I do not think, a new option in the chart is the correct way.
Comment 3 Heiko Tietze 2018-04-04 18:10:47 UTC
No further input, so closing as WONTFIX.