Yeah, I noticed the dates, and also the total amounts as well (due to adding columns). One other thing I did was add an option for all dates, using min/max of transactions dates and putting that into the date filter fields.
1 Like
I like it. Added to the template.
Iām not sure what Iām missing, but how do I actually set a custom date range? I can select ācustomā from the dropdown, but then what? Seems to default to December 30, 1899 - December 30, 1899, and I donāt see a way to change that?
Woops, you found a bug! Somehow the date input fields got lost in the process. I just added them to the template. You can either copy the sheet from the template linked in the first post in this thread, or wait until the change is approved in the Community Solutions extension, so you can do a Restore to update the sheet. Once updated, when you choose āCustomā, date fields will appear below the other options in the center column.
Hi @jpfieber ,
Great comparison tool.
I am running into an issue where it looks like the filter is expecting there to be a Group column on the Transactions sheet, however, mine does not have a group column.
How can I make it work to allow for Group filtering?
Thanks,
AHB
Thank you for that. I have added it and it works beautifully.
1 Like
I donāt see the link for Google Sheets (other than for Excel). Sorry, a bit of a noob but looking for the link name or triangle and missing itā¦
Sorry, I found it in the extensions. I am looking to compare all categories YoY simultaneously, eg Group = Discretionary Expenses, Categories include Dining, Travel, Entertainment. I donāt nec want to see the individual transactions, simply to compare my year on year discipline per category. Do you know a way to accomplish that? Currently doing it manually, eg by 2020 2021 (and then copying and pasting the values) and then automating for 2022. Itās very useful to see trends, eg as kids grow up Child Care goes down but our Travel increases.
Looks like this one hasnāt had much activity in it recently. I just added it to Google Sheets and am getting the following Value# error under period Error "Unable to parse query string for Function QUERY parameter 2: PARSE_ERROR: Encountered " "s āā at line 1, column 179. Was expecting one of: āgroupā ā¦ āpivotā ā¦ āorderā ā¦ āskippingā ā¦ ālimitā ā¦ āoffsetā ā¦ ālabelā ā¦ āformatā ā¦ āoptionsā ā¦ āandā ā¦ āorā ā¦ā I have a Tags column in transaction sheet thatās been working everywhere pretty well until this. Thx
I just installed from the gallery and its working for me. I havenāt seen an error like youāre describing. Are you using Apps Script for anything? Sounds like a scripting error. Could you include a screenshot of what youāre seeing?
Is it also possible that there is a quote string in the text that Google reads as the close of the statement?
1 Like
@MoMentum and @jpfieber - Iāve seen that kind of error before when the underlying data contains a possessive apostrophe s ('s) - ex. a category name like Johnās Lunch rather than Johns Lunch. I havenāt looked at the template, but if a query function is assembled programmatically, such an apostrophe can trip up the QUERY function and throw an error like that. Aside from removing the apostrophes from the data, a possible solution on the template side is to use double quotes in lieu of single apostrophes. Maybe it helps resolve that issue.
Trying to show a screen shot. Iām not using apps scripts. I may just try to delete it and try it again.
Hopefully deleting and reinstalling does it. I tested @KyleT theory, but I have transaction descriptions with quotes and apostrophes and they come through fine. I also thought maybe things in brackets (eg. ) might break it, but that still works as well.
If the problem persists, maybe @MoMentum can look in Column AB and see if thereās a hidden Transfer type Category listed that contains an apostrophe. Based on the screenshot, I think that this might be the issue.
A similar error will also get thrown when a Tag containing an apostrophe is selected in the dropdown, but thatās not your case.
Replacing the single apostrophes in the Cell B4 query function in just that one part (ā¦NOT Col3 MATCHESā¦) with double quotes, as shown below, might immediately resolve the issue.
But all of the single apostrophes in the whole formula could just be replaced by double quotes to make it immune to other possible cases - except the Labels at the end, those could be left alone. I only just recently figured this out on another project that I was working on. Hopefully it helps.
and NOT Col3 MATCHES '" & TEXTJOIN("|",true,AB12:AB) & "'")
and NOT Col3 MATCHES """ & TEXTJOIN("|",true,AB12:AB) & """")
Thank you KyleT.
That worked. I need to check it closer if it completely fixed the issue and everything is now pulling but it looked like it after I removed apostrophes from all categories and tags. I started replacing the apostrophes in the formula too but i got an error message saying something like I didnāt have enough parentheses and if i didnāt want a formula I should start with apostrophes or something. Fortunately I exited out and it went back to the original.
Thanks again for your help.
Mo
āIf the problem persists, maybe @MoMentum can look in Column AB and see if thereās a hidden Transfer type Category listed that contains an apostrophe. Based on the screenshot, I think that this might be the issue.ā
2 Likes