I have a workbook where I track game stats for my local community. I added a chart that changes upon a few selections and I use filter to get the desired result. The data comes from a sheet where I use query to calculate month to month differences (since I could not find this easily done with google's provided pivot options). One of the query's looks like this
=query('Response Edits'!1:1112,"select A,B,C WHERE A IS NOT NULL AND NOT H matches '"&textjoin("|",TRUE,query('Response Edits'!1:1112,"select min(H) WHERE A IS NOT NULL group by D",0))&"' order by D, C ASC",0)
- A converts the month value in the timestamp to the correct survey month (e.g. a 2020-07-01 would be for 06 survey and 2020-07-29 would be for 07 survey)
- B converts the year value in the timestamp to the correct survey year
- C is the timestamp of the survey submission
- D is the player name
- H is the player XP of the survey submission (I use this as a lazy solution since it only increases and because I could not figure out a
way to include the key phrase date using multiple datetime e.g.
NOT C matches date texjoin("|",TRUE,"select min(C)...")did not work)
the textjoin is just to remove the earliest date submitted because it would not have a month to month value. Here is a portion of the output of the query above and another query which I believe is correct:
7 2020 2020-07-31 23:18:48 ... 6873449 198 11610
8 2020 2020-08-31 22:15:53 ... 7789713 175 8732
9 2020 2020-09-30 23:03:12 ... 5994347 139 8932
When I close the the sheet and reopen it I notice that my chart has only 0 values because my sheet with the query functions is only outputting 0. The above query and my other query have also given a different output, which I have provided a portion for below:
6 2020 2020-06-30 22:04:02 ... 0 0 0
7 2020 2020-07-31 23:18:48 ... 0 0 0
8 2020 2020-08-31 22:15:53 ... 0 0 0
9 2020 2020-09-30 23:03:12 ... 0 0 0
I am new to using query, but the formula seems correct, because if I change the last 0 in the formula (which is the option for header) to 1 and then back to 0 I get the desired result.
Tl;dr Why does the queried data not output correctly when I close and reopen a workbook? And why does it output correctly after the formula is changed and changed back (including selecting undo)? Is it potentially textjoin or matches causing the problem in the query?