CODEWITHSUNDEEP
google-sheetsspreadsheetgoogle-sheets-query
Fernan
Fernan

Reputation: 77

aggregate results by week, month for graphing using sheets

Is there a way to convert daily values into weekly averages for charting?

Sample source sheet

---------------------------------------------------
| Data 1 | 1/1/2018 | 1/2/2018 | 1/3/2018 | etc...365 days |
---------------------------------------------------
| row 1  |    25    |   30     |    5     | etc...| 
---------------------------------------------------
| row 2  |    2     |          |    4     | etc...|
---------------------------------------------------
| etc ... each row has a different cadence.

Except for, each row may not have values daily, but rather weekly or monthly, depending on the cadence. So I would like to use the query function to aggregate some rows as weeks, and some rows as month.

Expected results for a weekly aggregate (if there is more than 1 value I just want the avg for the week, IE: if in a week there are values: 2,3,3, simply show: 2)

---------------------------------------------------
| Data 1 | Week 1 | Week 2 | Week 3 | etc...52 weeks |
---------------------------------------------------
| row 1  |    25  |   30   |    5   | etc...| 
---------------------------------------------------
| row 2  |    2   |   0    |    4   | etc...|
---------------------------------------------------
| etc ...

for a monthly aggregate (if there is more than 1 value I just want the avg for the month, IE; if in a month there are values: 25,50,100, simply show: 58) 

---------------------------------------------------
| Data 1 | Jan | Feb | Mar | etc...12 months |
---------------------------------------------------
| row 1  | 50  | 30  |  55 | etc...| 
---------------------------------------------------
| row 2  | 2   |  0  | 4   | etc...|
---------------------------------------------------
| etc ...

This way, I can create graphs showing the correct unit of measure (week, month, etc). How could I convert?

Upvotes: 3

Views: 4489

Answers (2)

player0
player0

Reputation: 1

for week:

=ROUND(AVERAGE(ARRAYFORMULA(VALUE(QUERY(
 {ARRAYFORMULA(TRANSPOSE(WEEKNUM($A$1:$AE$1, 2))),
  TRANSPOSE($A2:$AE2)},
 "select Col2 where Col1 matches '"&COLUMN()&"'", 0)))), 0)

for month:

=ROUND(AVERAGE(ARRAYFORMULA(VALUE(QUERY(
 {ARRAYFORMULA(TRANSPOSE(MONTH($A$1:$AE$1))),
  TRANSPOSE($A2:$AE2)},
 "select Col2 where Col1 matches '"&COLUMN()&"'", 0)))), 0)

3

Upvotes: 0

Max Makhrov
Max Makhrov

Reputation: 18717

For months:

=TRANSPOSE( QUERY( TRANSPOSE(Sheet1!A1:Y4), "select month(Col1) + 1, avg(Col" & JOIN("), avg(Col",ARRAYFORMULA(row(INDIRECT("a2:a"&COUNTA(Sheet1!A2:A)+1)))) & ") group by month(Col1) + 1") )

select part will produce query string like this:

"select month(Col1) + 1, avg(Col2), avg(Col3), ... group by month(Col1) + 1"

The result:

sum(month(Data1)1())    1     2 ...
avg row1               50    50
avg row2               25    13
avg row3               11    15 ...
...

Upvotes: 0

Related Questions