[Solved] Chart with date on X-axis in Calc

[Staffan]

Hello!

I'm an newcomer to OpenOffice and I'm already a big fan of it.

But I have one problem:
I have tables in Calc with dates in the leftmost column.
The other columns contains different readings, for instance temperature and other technical measurements.
These readings/entries are not made with even time/date-intervals. Sometimes they are made once every day, sometimes there will be several weeks or months between readings.

When I make a Chart of this I get dates on the horisontal axis and the readings in the vertical axis. So far it's ok.
But, horisontally the readings are spred out evenly, not showing the real distribution of readings in time. There is no real "time-scale".
Earlier I have made similar charts in Excel 2000, and there the data shows accurately distributed in time.

Any ideas about how to solve this in Open Office?

[r4zoli]

Welcome to the forum!

Can you upload an example file in excel format to show the chart, what you try to reach in OOo.
The file size limit is 128k, and without sensitive data.

[FJCC]

There are a number of things that could cause this, so uploading a file is certainly a good idea. A quick thing to try is to confirm that you are selecting the chart type XY (Scatter) and not the type Line.

[gkbOoocf]

Hope I'm not breaching etiquette by jumping in with a similar question and attachment ahead of the original poster. I hope Staffan will still come back with his own attachment/further queries so that, hopefully, his own specific needs can be met.

The attachment shows some dummy dates and data just so people can see where I've got to now (not very far) and perhaps understand better where I'd like to get to.

I don't particularly like the dates stuck horizontally in the middle of the graph, which is presumably a consequence of the graph showing both positive and negative values. I think I'd prefer the dates to be shown below the graph, ideally rotated (like the title on the spine of a book) so, perhaps, the graph wouldn't need to be stretched out so much to avoid overlapping of dates.

Ideally, I would have notches on the X axis (along with vertical guidelines), with the notches labelled to show at least one date per year, e.g. 1 January 1982, 1 January 1983 and so on (and not the dates the program has currently chosen to display which do not represent an easily recognisable sequence).

If space on the X axis allowed (without having to have a vastly wide graph or overlaps in dates) I'd welcome being able to show an intermediate date as well e.g. 30 June 1982, 30 June 1983 and so on.

I expect I'd be asking for something a bit too sophisticated if I asked for the points on the graph to be labelled with the values they represent, either permanently or as tooltips (value shown when point pointed to with mouse).

I wonder if anyone reading this is able and willing to guide me as to how I might be able to achieve a result more like what I describe above?

[mriosv]

Find attach the file with a little changes. If it's what you like, you can do it by double pick on the graph, double pick on the X axis, then you can put labels, the scale and other things as you like.

[gkbOoocf]

Thank you very much. That's a significant improvement.

I don't particularly like the dates on the X axis taking up a lot of vertical space but much prefer them below the graph than in the middle. Would prefer the date to be rotated like the text on the spine of a book but appreciate that that might not be possible with the Calc program as it is at the moment.

I would prefer it if I could set the range of dates shown on the X axis and fix them at more clearly recognisable intervals. I can see that the years are 5 years apart but the sequence as regards day and month is less easy to follow. I suspect the dates shown may actually be a fixed number of days apart, though I would prefer the notches/vertical guidelines along the X axis to correspond to month or year boundaries. Unless the present graph was made significantly wider, there wouldn't be room to display even each year along the X axis, let alone each month, though showing notches/vertical guidelines at intervals of, say, 3 years instead of 5 might work at the present size.

If rotating the text of the date on the X axis is not possible, so the text is on its side, just showing the last two digits of the year (and not the day or month) vertically: 82, 87 etc. would look better in the present graph. I wonder if that is possible.

[mriosv]

Find attach the file with a little changes. If it's what you like, you can do it by double pick on the graph, double pick on the X axis, then you can put labels, the scale and other things as you like.

Do you try to do as I have write.
In the tab "Label" you can set the direction of text.
In the tab number you can set the date format, with list for select, or set your own.
Also you use [F1] to get help.

[gkbOoocf]

Thank you very much for the explanation. Sorry, I didn't initially pay enough attention to what you wrote, focusing more on the chart you attached. Your further clarification was a help though.

I attach a further chart showing the X axis labels rotated as I wanted them and with the start and end of the date range chosen by me using the Scale tab.

It's just slightly disappointing that it seems I have to set an interval of a fixed number of days on the X axis, so I can't set the notches on the X axis to year beginnings or ends (or month beginnings or ends). Setting the start of the range to the first day of a year and setting an interval of 365 days fails to achieve the desired effect of labelling each subsequent notch as the first day of the next year (as can be seen from the attachment).

I note that there is an option to put labels on the X axis in the format of e.g. 1st Quarter 2009, which is potentially useful.

It would be nice if I could show two series of labels on the X axis with one series showing the year horizontally (positioned to the right of a notch corresponding to the start of the year) and the other series showing the month rotated to the vertical underneath the label showing the year. I see there is provision for major and minor intervals but I don't think there is provision for displaying, say, the major interval label horizontally and the minor interval label rotated to the vertical.

I appreciate that to show months as well as years, I would need to stretch out the chart a great deal more, or else show a much smaller range of years in the chart.

[Staffan]

Hello again!

To see what I ment in my first posting, please see the two enclosed dummy examples!

The Excel 2000 chart has a "true" timeline in the X axis, showing that the timespan between readings vary a lot. Every month is equally spaced horisontally.

In the Calc Chart there are no real timeline, but the readings are spread out evenly and the dates from the leftmost column are just used as any other labels.

Is there a way to create a "real timeline" in a Open Office chart?

[FJCC]

Staffan,
Yes, you have to change the type of chart to X Y (Scatter). Double click on the existing graph to put it in edit mode, then right click on it, choose Chart type and selelct X Y (Scatter). You may then have to edit the data series. With it still in edit mode, right click and choose Data Ranges. On the Data Series tab click on the "X-Values" within the Data ranges list and then fill in the box for Range for X-Values. The attached file shows the result of me doing this. Alternatively, you can just start over making the graph and choose X Y Scatter from the beginning.

[Staffan]

That did the trick! Thanks a lot!

[DGerman]

I cannot seem to get this to work. Sorry to be "thick". Manually entered data as 5/1 , Calc correctly presented 05/01/10 assuming I meant a date.
With datapoints 05/01/10,[/b]1; 05/03/10,2; 05/08/10,3; 05/23/10,4; 06/06/10,5; 07/20/10,6; 07/22/10,7; 07/25/10,8; 08/04/10,9 .
Highlight cells A1:B9 ; Insert/Chart/X-Y scatter/Finish presented nice chart but first X-axis label is 3/13/10, second is 05/02/10, then 6/21/10, 8/10/10 and 9/29/10. (not nice)
Format axis scale minimun 05/01 produces first xlabel at 05/01/10 but second (automatic max and interval) at 6/20/10 and third and last at 08/09/10. Still Not nice.
Format axis interval 7; 05/01/10, 05/08/10, 05/15/10, 05/22/10, 05/29/10, 06/05/10 ... Still not nice ( but that's what I asked for)
Of course adjusting the interval doesn't help.
Desired labeling is 05/01/10 ... 06/01/10 ... 07/01/10 ... 08/01/10

[acknak]

I'm not sure it's possible to get an XY chart with a proper date axis as you want. Chart always wants to use an even spacing on the axis and "nice" dates don't fall on a regular spacing.

Edit: PS:

There is a request for this: Issue 96937: Cannot set free (like 1 month) major interval if axis is formatted as date/time
You can register there and add your vote (up to two) or comment.

[Staffan]

As far as I can tell, there is no way to set X-axis intervals to number of months, which is what you seem to be looking for.
I have tried the same. An approximation (but not "nice") is to set x-axis interval to 30 days.

So, maybee a developer could look into this? Would it be possible to add a possibility to set not only days as an interval, but also months ?

[acknak]

There are no developers here. If you want to make a suggestion to the developers, use the issue tracker: [Tutorial] Reporting bugs or suggestions. I gave a link to the specific issue that covers this; you can add your suggestion as a comment there.