Description: When trying to compose a chart with a TIME scale on the X Axis, the resulting chart is incorrect (Read totally messed up). Under no circumstances can I get the data to correctly relate to the time points, or get the time axis to autoscale correctly. Steps to Reproduce: 1.Generate two columns of data, Col A is 00:00:00 to 23:45:00 in 15 minute intervals, format as time. 2. Column B is numerical data, 1 ..96, or constant with a few different points for calibration. 3.Select Both columns, and then Insert>Chart>x-y scatter chart, finish. Actual Results: chart is scaled from 12:00:00AM to 12:00:00AM, with Y axis centred.Chart range covers 48 hours, with x axis auto-scaling to 4800:00:00. If you force scale to 24 hours (00:00:00 to 24:00:00) then data is not correctly related to time. Expected Results: x axis scaled from 00:00:00 to 23:45:00 (or 00:00:00am), with y axis on left hand side, x axis covering 24 Hours, not 48. Reproducible: Always User Profile Reset: No Additional Info: file to be attached, containing sample data.
Created attachment 189620 [details] test sample data wiht results I've tried a lot of different time formats, but none produce a viable result. Also, I started with numeric data in col A, then converted this to time format which also gave strange results.
I could be wrong but you might want to try changing the Chart type from x-y (scatter) to "Line" (or start the wizard to insert a new chart). I’m not sure this is always a good match; YMMV and do it at your own risk – you shall not blame anyone else if in this and/or some other case the result is not adequate. Please remember that "time" in Calc is actually a numeric value between 0 and 1, that is displayed as "time" (of day/date). I'm not sure we see a bug here, but rather a question more appropriate for https://ask.libreoffice.org .
Hi Ady, OK. changing line type to line does work - BUT - you still can't change the format of the time in the x-axis- selecting e.g. 01:37PM as the format:- the output still includes the date. However, if you take the date off the column, so as it is HH:mm:ss rather than dd-mm-yyyy HH:mm:ss it all falls over again. I need to be able to format the axis to HH:MM. I' don't comprehend the difference between line and x-y scatter, as they seem to produce similar results{, but have different axis format menus} As a suggestion, the Axis format should have both an input format specifier, and an output format specifier: The code would then know how the data in the column is formatted, and also the required output. regardless of the chart type the axis format should be able to read the data from the column, it currently cannot === BUG. Mike.
Additional: XY scatter and Bubble fall over with all date / time, time data, ll the other chart types look ok with date/time format, BUT fall over when only time is present (quick test).
Further: If the data for the graph is on a different page, Line chart type falls over too.
Seems to me the issue comes from the data. There are no hours from the same date, but from different dates. Format hour values with 'DD-MM-AA HH:MM:SS' to see it. I think this is the source of a wrong scale values.
This did not work with the existing chart and data. ( the DD-MM-AA HH:MM:SS format) However, I did create a new set of time stamps using fractional numeric values, and then format them to time., and created a new chart, this gets the axis right. So it looks like the problem is correctly reading the time format from the text values in the cell.
(In reply to MikeD from comment #3) > regardless of the chart type the axis format should be able to read the data > from the column, it currently cannot I guess _that_ is the key part of this ticket. I mean, users might want to have an axis of the chart displayed in a different format than the format used for the data itself. Such expectation is reasonable: an adequate formatting for a chart might be different than an adequate formatting in cells/table. I am not sure whether the issue is about: A. Is there really a bug? B. Is the feature not yet supported? C. Is the problem about knowing (or clear instructions) how to do it? At any rate, I believe we need clear(er) "steps to reproduce" (or "expected steps how this feature should work").
Hi Ady, Your latest in comment #8 is essentially correct. Is it a BUG: yes in that what appears to be correct data does not produce the logical result. It is also, to some extent a feature enhancement requirement: In that time formatting is so variable,one needs to specify both an input and output format - possibly also with a test to translate the applied format on the source data. It does also reflect a loophole in the on line documentation for this aspect of calc: On line searches shows numerous threads across different sites with users having trouble with the time format - one even including "resort to windows/excel as this is not possible with calc" or to that effect. As regarding the steps to reproduce, I have detailed those above, repeated herein, and also included the sample file demonstrating the erroneous result. Steps to Reproduce: 1.Generate two columns of data, Col A is 00:00:00 to 23:45:00 in 15 minute intervals, format as time. 2. Column B is numerical data, 1 ..96, or constant with a few different points for calibration. 3.Select Both columns, and then Insert>Chart>x-y scatter chart, finish. I am also frustrated to a certain extent in selecting the format axis option when the chart area is selected. There seem to be diverse selection modes with different menu options that are exclusive. If charts get an overhaul then I feel that this could be improved too.
scratch last on menus - this is caused by single or double clicking to edit and is related to the double click speed.
(In reply to MikeD from comment #9) > 3.Select Both columns, and then Insert>Chart>x-y scatter chart, finish. That would bring whichever "default" behavior/formatting is set in each type of chart. In order to have a different (time) formatting in the chart than in the cells/data, there must be some additional STR. Either the chart wizard would need more steps (or more options) to customize the resulting chart, or the user needs to customize the resulting chart by performing some additional actions (e.g. editing some property of the x-axis). These are examples of additional (and detailed) "steps to reproduce" (STR) that seem needed in this ticket. This is the intention of my last sentence in comment 8.
Hi Ady, sorry if I seem a bit dense: Yes this does bring in the default behaviour of charts>> which in this case is "use source formatting" which was set in step 1. This should produce some output which is related to the input data even if it's not the final required result:: It does not do this - see attached file. However, If you wish to proceed beyond the default then: 4) Double click the chart area to select edit mode (grey border, black handles) 5) Click on the x axis to select (Blue corners to axis) 6) right click format axis OR Format> Axis> X- Axis to get the menu for formatting the axis 7) Select the scale tab and observe that the automatically selected period for the chart is 4800 hours. 8) Select the numbers tab, uncheck Source format box, Select Time from Category, and e.g. 01:37 PM from format. click OK and observe that the axis labels do not correspond to the input data. repeat 8) with any format of your choice.
Further 10) go back to the Scale tab, uncheck automatic for Min, max and major interval, set these to 00:00:00, 24:00:00, and 1:00:00, check OK and observe that the displayed period on the chart does not correspond to the 24 hours of data but to a much smaller (1/100th) interval.
Thank you all. (In reply to m.a.riosv from comment #6) > Seems to me the issue comes from the data. > There are no hours from the same date, but from different dates. Thanks for spotting that, m.a.riosv. (In reply to MikeD from comment #7) > However, I did create a new set of time stamps using fractional numeric > values, and then format them to time., and created a new chart, this gets > the axis right. Glad that works now. (In reply to ady from comment #8) > I guess _that_ is the key part of this ticket. I mean, users might want to > have an axis of the chart displayed in a different format than the format > used for the data itself. Such expectation is reasonable: an adequate > formatting for a chart might be different than an adequate formatting in > cells/table. We do have that in axis formatting > Numbers: we can use "source format" or untick that and pick any format. (In reply to MikeD from comment #9) > Is it a BUG: yes in that what appears to be correct data does not produce > the logical result. As m.a.riosv mentioned, the underlying data is not what you thought it was. We can not expect functions to base their calculation on how the data is formatted instead of the actual value it holds. Just as one example, different people will interpret one single formatting as different things. Is 1,234 a thousand marker or a decimal marker? Is 3/12 a division or a date? etc. So "logical" will be different from one person to the other. We have to rely on actual, underlying data. If you are forced to work with "bad data" for whatever reason, there are functions that might help get to clean data, for example extracting time data with hour(), minute(), second(). > It is also, to some extent a feature enhancement requirement: In that time > formatting is so variable,one needs to specify both an input and output > format - possibly also with a test to translate the applied format on the > source data. If there is an enhancement request, please open a new report as this report has already become too convoluted. (And link it here.) In any case, the original issue is "not a bug" as it was a case of bad data hidden by the formatting.