Google-sheets – Fill in missing dates in Google Sheet query

google sheetsgoogle-sheets-query

I have a Google Sheet query like this:

=QUERY(A2:I;"select I, sum(C) * -1, count(C) group by I order by I ASC"; 1)

And it yield a result like this:

enter image description here

In the source data not all months are occur. But in the aggregation I want to see all the months. How can I manipulate the results or the query, so I have all the months. See example output:

enter image description here

BTW: If you are asking why 2020-11 is showing up. This is due to the fact that in this month there was exactly the same amount of negative and positive, resulting in 0.

Best Answer

One approach would be to wrap your existing QUERY in an extended formula:

=ArrayFormula({"Month", "Amount"; DATEVALUE("2020-"&SEQUENCE(12,1)),IFERROR(VLOOKUP(DATEVALUE("2020-"&SEQUENCE(12,1)),QUERY(A2:I;"select I, sum(C) * -1, count(C) group by I order by I ASC"; 1),2,FALSE),0)})

You see that the headers here are generated in the opening of the formula.

DATEVALUE("2020-"&SEQUENCE(12,1)) will create a sequence of dates covering 12 months (12 rows) in one (1) column.

VLOOKUP will lookup each element of the above SEQUENCE in your QUERY; and IFERROR will assign 0 for any months that are not found.

Related Topic