0

Say I have events that occur on sporadic dates, like this:

Date Qty
2022-01-01 100
2022-02-08 200
2022-06-17 150
2022-08-03 700
2023-01-27 300

I have rows for arbitrary days in Jan, Feb, June, Aug this year, and Jan next year. I could even have multiple entries in a single month.

I want to insert a chart with the dates on the horizontal axis, and quantities on the vertical axis. The horizontal gap between data points should be proportional to the number of days in between each row of data. Something like:

                            700
                                ---- 
                          /           ---- 
                                           ---- 300
                        /
    200 ------         
   /          ----- 150
100
---------------------------------------------------
Jan Feb             Jun     Aug                 Jan

Is this possible, without having to insert empty rows into the table for every month where there is no data?

As far as I can tell, by default google sheets is spacing each data point equally across the X axis, regardless of how far apart the dates are. Here is a screenshot showing what I mean:

google sheets screenshot

Note that the columns are evenly spaced. In the data though, columns 1 & 2 are ~one month apart, while columns 2 & 3 are ~four months apart. I want the columns to be spaced accordingly.

5
  • Hi. Is this possible, without having to insert empty rows into the table for every month where there is no data? I converted your data to a period of 392 days (1 Jan 22 to 27 Jan 23) checked "Chart Style" = "Plot null values". There is no effect on the x axis.
    – Tedinoz
    Jan 12 at 4:21
  • I'm not sure that the actual chart created by Google is a lot/at all different to your example. Would you please include the actual chart created by Google and overlay how/where you expect that your version would be different.
    – Tedinoz
    Jan 12 at 4:25
  • Edit the chart, "Customize", "Grid lines and ticks", select "Horizontal Axis". Edit "Major count" = 10. This will insert a date on every major tick. I think this is proof that the "horizontal gap between data points should be proportional to the number of days in between each row of data."
    – Tedinoz
    Jan 12 at 4:32
  • @Tedinoz I've added a screenshot showing the problem. The columns are all evenly spaced in the chart - I want them spaced out proportionally to the dates. I don't see an option for "Major count" when I customise the chart. Jan 12 at 22:32
  • _ don't see an option for "Major count" when I customise the chart._ That is because you chose a "Column chart" instead of a "Line chart"
    – Tedinoz
    Jan 13 at 0:58
0

You want the horizontal gap between data points to be proportional to the number of days in between each row of data. I suggest that this is the result of the standard chart layout.


5 data elements

This is the chart created using five data elements.

chart1


362 data elements

This is the chart created by including a value for every day between 1 Jan 2021 and 27 Jan 2023. Data

Data

Chart Changes requires for this chart:

  • Customize, Chart Style, check "Plot Null Values"
  • Customize, Grid lines and ticks, select "Horizontal Axis", set "Major count" = 10.

chart2

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.