Oh also, you can select the group/category/merchant to drill into via the drop down, or just click on the Group/Category/Merchant name and it should automatically update the lower graph / transaction list.
I’m really excited about this one. It’s quite similar to the graphs and analytics from Mint I mentioned in this post.
Things from Mint that I would love to see included:
- Making it easily customizable (e.g., filtering by group or category)
- Filtering by account (and being able to select multiple accounts)
- Comparing expenses with a previous period (e.g., same period last year)
See this post for the rest of the examples.
Also, I generally find bar graphs to be the most useful of the graphs.
Thanks, @cps!
1 Like
This will let you filter by group and category on the income/spending by category pages.
Are you interested in those filters in the spending/income over time as well?
I can pretty easily add an account filter, but it’s quite difficult to do the multi-select in Google sheets in a compact way (would love suggestions if I’m missing something). I had previously done this with pivot tables and a slicer but it got unacceptably slow, so I took it out. Would the ability to filter by a single account be worth it to you, or do you need to be able to multi select accounts for it to be worth it?
Oh and and the spending over time graph does have a Y/Y comparison. Just wanted to make sure you saw that!
Thanks for all the feedback!
The ability to view a single account would definitely still be worth it. Viewing multiple accounts at once would be a great bonus—but even filtering by one account would be very useful.
Okay I will work on adding that and a group filter for spending over time.
I just made a major update to this template. New features include:
- Ability to filter by account, group, and category on all sheets (except for Spending/Income by Category where it felt confusing to me to be able to filter by sub-category, so it’s only for Account and Group on that one).
- Cleaned up query logic for anyone who wants to mess with that.
- On the Spending/Income Over Time tabs, now have the ability to list transactions in the selected month.
You should just be able to make a copy of this spreadsheet and update it with a link to your Tiller-linked sheet with a Transactions tab and be off to the races.
Thanks @ksh24 for the suggestions!
Thanks, @cps. On my original Transactions sheet, I added columns for Type and Group (that was necessary, correct?). While the type and group of each category populated after I added the formula in your instructions, for some reason I got error messages on almost all of them—both in the Type and Group columns.
The only time an error message does not appear are in the few instances where the name of the group and the name of the category happen to be the same.
A couple of things - it was pointed out that adding manual transactions also uses a column named “Type” so I renamed this to “Category Type”.
My guess is that the red arrows / error messages are because when you added columns to the left it carried the data validation rules over from the Category column. From the menu go to Data->Data Validation and find the data validation rule that applies to column F, and make sure it ONLY applies to column F – just edit the range to not include columns D and E.
Not sure if you got this working, but I made an update to my dashboards to handle this on the dashboard side so you don’t need to make any changes to your Transactions tab. New version (2.1) is available above.
You can delete those Group and Type columns you added now (and in fact should, or move them to the end so they don’t mess anything else up).
Hi- This looks like an amazing Dashboard and I am excited to use it. I am running into a bug that I am hoping you can help me with. Expense Groups and Expense Categories are not populating. I think it starts at the dashboard picklist config spreadsheet. Both columns return " Error - No matches are found in filter evaluation". If I look at columns ab and ac on the transactions sheet, those are populated.
Any ideas? I do have more categories and groups than you have in the sheet, but this error seems to start earlier in the process. Thank you!
Hi there! Everything is driven off the Transactions and Category tab, so if you are seeing errors on the Picklist tab something is going wrong there. Do you see any errors on either the Transactions or Category tab? Does it look like all of your Transactions columns have imported correctly? Do columns AB and AC on the Transactions tab contain the transaction Type and Group for the selected category?
On the Categories tab do you have columns named “Category”, “Group”, and “Type”? It’s expecting columns with those headers.
Also, it only imports the first 3 columns of your category tab. If you have Group, Type, and Category columns that are NOT in the first three columns of your Category tab, you can change the formula in A1 of the Dashboard Categories tab to import more than “Categories!A:C” – you should just be able to add additional columns there and the right things should happen everywhere else.
Thank you so much for your response. I think ultimately it came down to a mistake we made with the Types. We had altered them to stratify expense types. Once we changed them back to the tiller expenses everything worked itself out. Looking forward to digging into the spreadsheet!
1 Like
Cool. I made that mistake early on. Tiller doesn’t handle anything other than the preset types very well (which makes it a bit odd that they even allow us to add other types).
Yeah, “Expense” vs. “Income” vs. “Transfer” as the Type system is pretty deeply embedded into this dashboard as well. I have used Groups and Categories to pretty extensively break things down. I have 115 categories over 25 groups in my personal system.
1 Like
@cps I’m still seeing this issue with v3.3.
On a related note, is it possible to have all tags deselected by default?
It is that way for the Dashboard tab, but on my Spending Over Time tab, only the first listed tag is deselected (all the rest are selected).
The default view would be to see all transactions/balances, besides categories/accounts that are marked Hide.
Hmm very odd. The comparison should be happening to the right month now (it’s a lookup on the month so I don’t know how that could be wrong). Is it just things missing for months you should have income or expenses? Are you sure you don’t have filters excluding those transactions?
I have expenses for all months. If I deselect all tags, then I get all months.
Here’s the Filter title. The tags are filtering - but that shouldn’t remove months from the comparison, right?
In the “Net Worth Over Time” tab, the hidden column AH has #NA “Did not find BALANCE in match evaluation”.
I don’t see any other obvious errors.