7

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, 2022 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, 2022 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, 2022 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, 2022 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, 2022 at 0:58

3 Answers 3

3

You want the horizontal gap between data points to be proportional to the number of days in between each row of data.


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

4
  • 1
    Yeah so basically what I want is the chart in your second screenshot, using the data from your first screenshot. From my original question: "Is this possible, without having to insert empty rows into the table for every month where there is no data?" Jan 15, 2023 at 2:07
  • My apologies. In general terms, the answer to your question is "no" BUT... I have just been reading the documentation regarding "Customizing Axes" and specifically Discrete vs Continuous. I think that a "Continuous" x-Axis might be the solution; problem is that the answer involves writing a script. There are some hidden traps (and workarounds) too- refer Hiding 0 value data labels in chart.
    – Tedinoz
    Jan 15, 2023 at 6:47
  • No worries, even knowing that it's not possible (without scripting) is a helpful answer. Cheers. Jan 16, 2023 at 23:24
  • 1
    @CamJackson By using a combination of sheetaki.com/… to populate all the dates using a one line formula, and IFNA(VLOOKUP(... to reference to your original table you can create a new table with the in the necessary format with minimal effort.
    – Chris A
    Aug 3, 2023 at 22:47
1

I know it is a late response, but for those searching for the answer as I just did:

This is possible and automatically done if you choose Line Chart, Area Chart, and their "Stacked" variations. It doesn't work with Combo Charts and Bar Charts.

1

Thank you to Tedinoz for the link to Discrete vs Continuous.

Once I discovered this link, I solved the issue I'd had for several hours within a matter of minutes.

The link makes it clear that

In line, area, bar, column and candlestick charts (and combo charts containing only such series), you can control the type of the major axis:

For a discrete axis, set the data column type to string. For a continuous axis, set the data column type to one of: number, date, datetime or timeofday

Now realising that this was actually possible, I experimented and discovered that you need to UNCHECK the box that says "treat labels as text" at the bottom of the Chart Editor -> Setup tab. By unchecking this box, Google Sheets interprets your x-axis values as continuous, at which point the axis becomes uniformly distributed in time/date/number.

It seems that for the chart type that I had (Stacked Column Chart), the default behaviour is to treat labels as strings, which is understandable.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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