Forum Discussion
Regular ticks for X-axis when data is irregular
I have some time series data in roughly this form:
Date Value
1/1/19 204
1/2/19 181
1/3/19 167
3/21/19 265
9/19/19 180
9/30/19 166
etc.
The point is that the dates are not regularly spaced. For example, three days in a row, then a gap of a few months, then weekly data, then another gap, then daily data, etc.
When I chart this as a normal line chart, the X-axis shows the same distance between each adjacent data value, in other words, there's the same horizontal space for a gap of a day, as for a gap of three months. So the chart gives a misleading visual impression of how the data trends over time.
What I'd like is a chart with an X-axis where the horizontal distance is constant for constant time periods. So, for example, I'd like to have a tick for the first day of each month, with the Value data blank during periods when it doesn't exist.
Any suggestions?
Thanks.
4 Replies
Thanks very much.
But I still can't get it to work the way I want it. When I change the chart type to "X Y (Scatter), the X-axis labels are no longer dates. Now they're numbers, corresponding to the number of points to be charted. I have about 220 data pairs (date & value), in 220 rows, and the X-axis labels go from 1 to 220, by 5's (5, 10, 15, 20, ..., 220).
What I'm aiming for is to get the X-values to show dates, rather than numbers. The X-axis values would be something like:
6/1/2019
7/1/2019
8/1/2019
...
1/1/2021
2/1/2021
OraMcCreary My guess is that some or all of your "dates" are really text. In the attached is an example showing how it will automatically use dates (note: the x axis is actually in days with the x-axis number format set to date. This is why the tick marks are not like 2/1/19, 4/1/19, 6/1/19 but instead actual even intervals based on # of days)
Since you haven't attached a sample file I would just recommend checking the date column to make sure all those values are actually dates. I hope that helps.
