So I’m new to Tiller and am absolutely loving it, but there’s one way of viewing my data that is really important to me that I haven’t figured out how to get. I’m trying to get disciplined in my discretionary spending and so want to monitor particular categories on a weekly a basis. I don’t want to include categories like my income or non-discretionary categories like medical costs or car repairs. I tried to use the “Weekly Trends” add-on and just create a formula at the bottom of the sheet to add the categories I was interested in, but when I would come back to it later the sheet would be blank and I would have to rebuild it. Is what I would like to do possible? Can anyone point me in the right direction as far as where to start?
Hi @guffia, Welcome to the community! I’ve been using Tiller for awhile but hadn’t yet checked out Weekly Trends and it seems like a really useful report for your objective. I like to think almost anything is possible, so here’s an example of two tweaks you could make that might get you close to what you’re looking for.
See the screenshot attached at the end.
Solution #1 - Add a summation row at the top and select the categories you want to sum (yellow screenshot highlight)
This sounds similar to what you were doing already. One potential pitfall with adding the summation row at the bottom is that the output of the query formula that is building the results table may collide with your sum row and maybe this is why it would blank out on you. I’d recommend putting it at the top- for example Row 2 just above the other totals. I had to unmerge the sheet title cell first as it was taking part of that row 2 real estate.
To make the sum row work on your desired categories and maintain flexibility, you can add checkboxes in ColA. Then you just need the following formula in row 2 (for my example it’s in cell D2) to do the summation for each column. You can drag this across for the 5 weeks.
=SUMIF($A$5:$A,TRUE,D5:D)
Solution #2 - Add a selector to hide certain categories on this report only (orange screenshot highlight)
Start by unhiding row 5 and the columns to the right of I.
Add 3 columns to the far right.
The first column “Hidden from THIS report” will identify those categories that you don’t want to see on this report only - it will write “Hide THIS” for items having the categories that you select. This column will be used to filter them out of the main report on the left side.
The formula in Row 5 of the first column will be:
=ARRAYFORMULA(IF(L5:L<>“”,IF(VLOOKUP(L5:L,P5:Q,2,FALSE)=true,“Hide THIS”,“”),“”))
The second column is the unique set of Categories that exist in this report. It will be used as the menu of categories to select to Hide from THIS report.
The formula in Row 5 of the second column will be:
=SORT(UNIQUE(L5:L),1,TRUE)
The third column will contain checkboxes to select those Categories of expenses to hide from this report.
You would insert checkboxes in that column and check off the adjacent Categories that you don’t want to see on the main report.
Finally the query in cell C5 would need to be retrofitted with the new “Hide THIS” element so that those items are excluded.
Before:
=iferror(QUERY(J4:O, “SELECT L, SUM(M) WHERE L <> ‘’ AND N <> ‘Hide’ GROUP BY L PIVOT J”, 1))
After:
=iferror(QUERY(J4:O, “SELECT L, SUM(M) WHERE L <> ‘’ AND N <> ‘Hide’ AND O <> ‘Hide THIS’ GROUP BY L PIVOT J”, 1))
You could finally re-hide row 5 and columns to the right of column I to have a clean looking report again.
Two cautions:
- I normally don’t like to have hidden selection criteria. So I placed a reminder to myself in cell C3 about the hidden Categories in this report using the following formula:
=COUNTIF(Q5:Q,TRUE) &" Categories are HIDDEN" &CHAR(10) &“See Column Q”
- Selector checkboxes can be a problem when your dataset changes size - ex. a new category is added, you have spending in a new category, etc., and so the box you checked may no longer align with the Category that you wanted to sum or exclude. It may require some maintenance. This could actually be a big detractor in some cases. A fix for that would be to hardcode a list, but that again would be subject to becoming fragile if you revamp category names. Just something to be aware of.
You could add a fourth column where you list the Categories that you don’t want to see. It’s Column R in my screenshot. Then you just need to replace the previous row 5 formula in the first column with:
=ARRAYFORMULA(IF(L5:L<>“”,IF(IFNA(VLOOKUP(L5:L,R5:R,1,FALSE),“”)<>“”,“Hide THIS”,“”),“”))
One last detail I like to do is just put a count in the heading of any checkbox rows so that I don’t need to scroll to see if I have anything selected off-screen. For example in ColA, I entered:
=“SUM CATS (”&COUNTIF(A5:A,TRUE)&“)”
Hope this is interesting and might help. Good luck!
Solution 1 looks like the perfect work-around. This did exactly what I was trying to accomplish. I feel kinda bad not to go with Solution 2 since you put so much work into it, but I have no doubt it will fit someone else’s use case. Your spreadsheet-fu is most impressive!
1 Like