OK, when I took a closer look at your modified query you kind of got the gist of it, but it was a point in the other query to actually make
theMonth into a string for comparison purposes.
When you want to look at the days separately, it’s not that much of a point, so it could most likely be simplified a little:
sum(map(rows.theDay, (r) => r)) as Created,
sum(map(rows.theDay, (r) => r)) as Closed
FLATTEN array([Created, 1, 0], [Closed, 0, 1]) as theDay
(contains(file.name, "INC") OR (contains(file.name, "SCTASK")))
AND !contains(Created, null)
AND !contains(Closed, null)
GROUP BY theDay as Day
WHERE dateformat(Day, "yyyy-MM") = dateformat(this.queryMonth, "yyyy-MM")
Also note the crucial difference of doing
this.queryMonth instead of just
queryMonth, to make it only read that value from the current file containing the query.
Alternatively, you could modify your query variant with a
WHERE clause of:
WHERE dateformat(date(Day), "yyyy-MM") = dateformat(this.queryMonth, "yyyy-MM")
This would both remove the error messages, since it’s converting the
Day which is a string back into a date, and compare it against the
this.queryMonth instead of a (non-existent) `queryMonth in every file.