0

I have a spreadsheet on google drive with a series of dates in a column, each date representing a particular event.

1998-01-07
1998-01-10
1998-01-22
1998-01-23

If I have two or more events on the same date, that date appear that many times in the column

2000-02-18
2000-03-12
2000-03-12
2000-03-12
2000-03-20

I'm trying to create a column chart with number of events by month without success.

1 Answer 1

1

First you should prepare your data to be plotted.

One of many ways is to use QUERY.

Example:

=query(A1:A9,"select year(A),month(A)+1, count(A) group by year(A),month(A)+1 label year(A) 'Year', month(A)+1 'Month', count(A) 'Count'")

NOTE: month(A)+1 is used because 0 is January, 1 February and so on, but people use 1 for January, 2 for February and so on.

Result

Year  Month Count 
1998      1     4
2000      2     1
2000      3     4

Then you could create a chart like the following:

2
  • Looking at the chart, versus the data, the chart is nonsensical. You wouldnt want the month as a Y-axis value.
    – enorl76
    Apr 4, 2019 at 17:22
  • Hi, I'm a newcomer to Google Sheets and SQL both, so I am curious about this.=query() looks like it is specific to the Google Visualization API. Is this normally all it is used for? Or is it just a query language that happens to be/is conveniently available within Sheets so it is used for other things besides preparing visualizations? Thanks.
    – 1252748
    Jul 13, 2020 at 13:15

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.