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

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:

| improve this answer | |
  • Looking at the chart, versus the data, the chart is nonsensical. You wouldnt want the month as a Y-axis value. – enorl76 Apr 4 '19 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 at 13:15

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.