Hi @taylorvance, Awesome solution! I just downloaded to try it out and no problems for me right out of the gate. Iām impressed by the use of pivot tables and the clever assembly of the Query function - Iām a big fan of using the Query function in Google Sheets. I was intrigued by the error and I was able to recreate what I think may be the same thing by introducing an apostrophe s ('s) possessive - i.e. turn a category name from John Lunch to Johnās Lunch. Based on the error message I think that may be a commonality, as it also broke my query function in D14 with a similar error. Note that this category also has to be a category marked as Hide. That extra apostrophe obviously messes up the syntax of the query function. Other than a category rename, maybe filtering the query range upfront is a possibility. Whatās still odd is that thereās no apostrophe s in the query outcome pasted by @th5418 but the error messages are consistent, so maybe it could be double-checked again.
Thank you @KyleT ! Very insightful. I updated the master sheet with a simplified query and offloaded hidden filtering to the pivot table. So yes, thatās definitely a bug but Iām not sure if we got them all.
@th5418 I appreciate your patience. Can you please try another restore? Did you have any hidden categories with apostrophes that were missed in the query copy/paste?
I did remove some categories with apostrophes from my snippet, sorry about that! Was removing some names and other categories I didnāt want in the public.
Ive refreshed and it works! Thanks so much @taylorvance and @KyleT
One last comment for now: other sheets hide the category if it is a Transfer type. I donāt mark them as hidden because Transfers are by default hidden. For example I have Credit Card Payments as a Transfer type, and it doesnāt show up on Budgets or Spending Trends.
I see Credit Card Payments in this add on.
@th5418 I didnāt know that about the Transfer type. Can you point me to some examples or documentation that deals with this? If it is the recommended way to deal with these then I can update the template. But I donāt want to overzealously hide categories that some people might want to see.
@mpresley410 @adekunledauda hopefully this resolves it for you too. Iāve notified Randy of this fix so you should be prompted soon to update your sheet. Thanks again for the feedback!
Yep, the documentation is on Tiller. Unfortunately I cannot put links in my response, but you can get to the page if you Google ātiller help center understanding transfersā.
This is under the āUnderstanding the Transfer category typeā header.
The Categories sheet Type column allows you to set a categoryās type as a āTransfer.ā By default, categories that are set as a Transfer type will not show up on your budget dashboards or affect your cash flow calculations.
I refreshed and itās fixed. As a note, I also had a hidden Category with an apostrophe. And, I assume you changed something with the Transfer types because I donāt see any of my transfers once I was able to see data. Thank you so much for the update.
Hi @taylorvance ,
This is a neat concept and worksheet, thank you for sharing.
In my version I modified the conditional formatting to create a bit of a heatmap type look.
If anyone else would like to apply the same look, you can accomplish it by adding an additional rule as shown in the images below.
Again, thank you for sharing.
AHB
This is a really nice report, thank you for creating and sharing it!
Tiller is a little slow so Iāve been removing empty rows but when I delete the rows beyond the report up to the end at row 20000 the report breaks. Can you tell me if I can safely delete any rows?
@rayfes check the hidden columns to the left (A-L). Thatās the staging area thatās bringing in your categories and transactions. Itās important to leave enough room for the max amount of transactions that will match the date range at the top.
Thanks for the quick response! I typically use it with a 3 year lookback which means 5017 transactions. Are you saying that I need to keep 5017 rows? I was just deleting rows after the report and not any columns (other than in the lower rows).
Yes, the report (pivot table) needs the hidden data to the left, and the hidden data needs enough rows to fill itself in. I would leave a buffer, not cutting off at exactly 5017.
That worked! I unhid columns A-L and left a few hundred extra rows below then deleted about 14000 rows without causing problems. Thank you!
I think this is an interesting and useful tool, but I am struggling a little with how the relative comparisons work. In the example below I just happen to have the same dollar amount in two different months in the same category, but they are highlighted as different colors. I guess my struggle is what exactly is the comparison trying to show.
@andy
They are based on percentile ranges for that column. The cutoff values for each color range can be found in the hidden rows above the pivot table. A cellās color corresponds to where its value falls within these ranges. Note that the display is rounded to the nearest dollar, which could mean that the actual values are slightly different, placing them on different sides of a percentile cutoff and resulting in different colors.
I really enjoy this app. I ran into a small problem with the display not showing March 2024. I have the app set to 2 years and it is showing every other month. I do have expenses in March, so I am not sure why they are not showing. Any ideas?
@jimlenz That is by design. I didnāt want to include the current month by default because it would throw off the stats (if the month has just begun, there wonāt be as many transactions). When we reach April, March will appear.
If you want, you can adjust the time ranges using the hidden formulas to the right of the percentile setup. Hint: change AB3 from =EOMONTH(TODAY(),-1) to =EOMONTH(TODAY(),0)
I had a feeling this was the case. Thanks.
Iām definitely interested in the upcoming version to drill down into transactions by double clicking.