Search code examples
chartsgoogle-sheetsdata-visualizationarray-formulasgoogle-sheets-query

Create Weekly/Monthly/Yearly line chart in Google Sheets to sum amounts within same week/month/year range

I want to create a line graph representing two columns of data: F, the date of entry, and H, the dollar amount. The date should be the X-axis, and the dollar amounts on the Y-axis.

The catch is that I'd like the dates on the line graph to represent the sum of all amounts entered in a given week, month, or year.

This photo is of YouTube analytics, which creates a similar graph to what I'd like to create in sheets:

https://imgur.com/a/fz51Rug

Similar to how these analytics give you the option to choose how you'd like the data (in this example, views, in my Sheets case, amounts)to be summed by the time it was collected, I simply want to make separate​ graphs to depict the different ranges of weekly, monthly and annually.

https://docs.google.com/spreadsheets/d/1P2vFfCVmsJwPLyD48YWQkwCR0jY3CPg7S9uOVlYhvkk/edit?usp=sharing This is a link to the type of data that I'd like to visualize.

Solution 
  • =ARRAY_CONSTRAIN(ARRAYFORMULA(
 IF(E1="weekly", 
 QUERY({"week "&WEEKNUM(A2:A)&" "&YEAR(A2:A), C2:C, YEAR(A2:A)+WEEKNUM(A2:A)*0.083}, 
 "select Col1,sum(Col2),Col3 where Col2 is not null group by Col1,Col3 order by Col3 label sum(Col2)''", 0), 
 IF(E1="monthly", 
 QUERY({TEXT(A2:A, "mmmm")&" "&YEAR(A2:A), C2:C, YEAR(A2:A)+MONTH(A2:A)*0.083}, 
 "select Col1,sum(Col2),Col3 where Col2 is not null group by Col1,Col3 order by Col3 label sum(Col2)''", 0), 
 IF(E1="yearly", 
 QUERY({YEAR(A2:A), C2:C}, 
 "select Col1,sum(Col2) where Col2 is not null group by Col1 label sum(Col2)''", 0), )))), 999^99, 2)

    0

    spreadsheet demo