t o n y a n g ' s w e b l o g

 2019JAN012019 with the start of a new year, i've had to update the master formula on our solar production google sheet document: =query('每天'!\$A:\$F,"Select Month(A)+1,Sum(B),Sum(C),Sum(D),Sum(E),Sum(F) where A is not NULL group by month(A) label Month(A)+1 '月',sum(B) '" & '每天'!\$B\$1 & "', sum(C) '" & '每天'!\$C\$1 & "', sum(D) '" & '每天'!\$D\$1 & "', sum(E) '" & '每天'!\$E\$1 & "', sum(F) '" & '每天'!\$F\$1 & "'") this was the original formula but it has a major flaw: it's sorting the values by months but disregards the year. so new values i input for january 2019 will be added to january 2018. =query('每天'!\$A:\$F,"Select year(A)+(month(A)+1)/100,Sum(B),Sum(C),Sum(D),Sum(E),Sum(F) where A is not NULL group by year(A)+(month(A)+1)/100 label year(A)+(month(A)+1)/100 '月',sum(B) '" & '每天'!\$B\$1 & "', sum(C) '" & '每天'!\$C\$1 & "', sum(D) '" & '每天'!\$D\$1 & "', sum(E) '" & '每天'!\$E\$1 & "', sum(F) '" & '每天'!\$F\$1 & "'") after a lengthy search, i found this rather elegant solution: convert the date into a decimal number that can then be grouped. e.g. 2018.12, 2019.01, etc. it works but it's essentially a hack that converts the date to a number. i rather keep the date as a date. =query({ArrayFormula(EOMONTH('每天'!A1:A,0)),'每天'!\$B:\$F},"Select Col1,Sum(Col2),Sum(Col3),Sum(Col4),Sum(Col5),Sum(Col6) where Col1 <> date '"&TEXT(DATEVALUE("12/31/1899"),"yyyy-mm-dd")&"' group by Col1 label Col1 '月',sum(Col2) '" & '每天'!\$B\$1 & "', sum(Col3) '" & '每天'!\$C\$1 & "', sum(Col4) '" & '每天'!\$D\$1 & "', sum(Col5) '" & '每天'!\$E\$1 & "', sum(Col6) '" & '每天'!\$F\$1 & "' format Col1 'YYYY-mm'") finally i came across this solution that uses the arrayformula to modify the date column in the query data field, using the EOMONTH end of month function to standardize all the dates before grouping them. but one thing i discovered is once i create a query data field using {} brackets, i can no longer refer to the columns by their letters but instead have to use Col1, Col2, Col3, etc. the query statement is also a sticker for capitalization, it took some trial and error to figure out that col1, col2, col3, etc. doesn't work. because EOMONTH was also converting blank fields to the default minimum date value (12/31/1899), i had to specifically filter out that specific date. this master formula also formats the date data directly through the query statement, e.g. 2018-12, 2019-01. we made 18.77 kWh today. it should've been more, but the sky was still cloudy this morning. by 10:45am the sun broke free and the production jumped to nearly 6kW, but a third of daylight was already gone. posted on Wed January 2nd, 2019 1:32AM | comments (0) | link