What is the goal of your workflow? What problem does it solve, or how does it help you?
Sometimes itâs useful to compare transactions from one time period against transactions from another time period. This allows you to specify two time periods, and then an optional Category and/or Tag to filter by.
How did you come up with the idea for your workflow?
Someone in the community was looking for this functionality, and I could envision what it might look like.
Please describe your workflow. What are the sheets? Does it use any custom scripts or formulas?
Copy the sheet from the link below to your Tiller Foundation Template by clicking the triangle to the right of the sheet name at the bottom, choosing âCopy toâ and then âExisting Spreadsheetâ. Choose your Tiller Foundation Template as the destination and the new sheet will appear there. It will automatically find your Date, Description, Category, Amount and Tags (if you have one) columns in the Transactions sheet as long as you have those column names. Only change the green highlighted cells. By default, it will compare all transactions from January of 2021 against those from January of 2022. You can change those start and end dates, and can filter the transactions down to a Category, a Tag, or both (only transactions that have that Category AND that Tag). A âTotal Amountâ appears at the top of each list to make it easy to compare the overall total of all the transactions shown.
Is it ok for others to copy, use, and modify your workflow?
Yes, and please let me know if you have an issues, or if you have ideas for improvements!
If you said yes above, please make a copy of your workflow and share the copyâs URL:
You can now find this in the Tiller Community Solutions extension, called âTransactions Comparisonâ.
Iâm guessing you donât have a âTagsâ column on your Transactions sheet? I guess I laid everything out assuming it would be there. The quick fix would be to add one. Iâll have to figure out how to handle sheets that donât have one though, since many people donât use tags.
OK, I figured out a quick fix if you donât want a Tags column. Iâve updated the template with these changes, so you could delete the sheet and recopy it, or you can change the following two cells in your existing sheet (youâll need to unhide the columns on the right side of the sheet):
Change cell S10 to =IFNA("'"&P$5&"'!$"&R10&"2:"&R10,"'"&P$5&"'!$A2:A")
Change cell Q13 to =IFNA(SORT(UNIQUE(TRANSPOSE(SPLIT(JOIN(",",FILTER(INDIRECT(S10),INDIRECT(S10)<>"")),",")))))
Basically, if I donât find a Tags column, I have the query look at the empty âAâ column on the Transactions sheet instead.
Deleted the tags, deleted the sheet. Copied a new one over and BAM! Worked perfectly But, now, with this sheet, Iâm thinking about adding some tags to my transactions⌠This is truly a very cool new thing. Thank you.
I updated the template to include a checkbox to âFilter Transactionsâ. This would only be useful if you werenât already filtering by Category. With the box checked, if you are filtering by Tag or have no filter, any transactions with Category or Group names that contain âTransferâ will not be shown.
I updated the template to include the ability to filter by Description as well. You can filter to only transactions that have descriptions that match what you type in, or you can get very powerful by using Regular Expressions (eg. .Google.) to filter down to exactly what youâd like to compare.
I really like this and already used it to find some discrepancies.
One thing I would ask if itâs possible to restrict those categories that are hidden. (For example I have some transfers that donât always balance out, or âdebt payoff receivedâ items.)
I updated the template to properly hide transactions with Categories marked as âHideâ on the Categories sheet instead of trying to match âTransferâ in the Group or Categories column. The checkbox is now called âFilter Hiddenâ, and is checked by default (which hides hidden transactions). This helps it behave the same as most of the other Tiller solutions. Uncheck the box if you want the list to include transfers and other âhiddenâ transactions.
Thanks, I saw that a few minutes after I posted.
I was in middle of working on a work-around so luckily I donât have to do that.
One suggestion for a feature is a drop down for date periods, so I donât have to manually change four dates, like âlast month, this month, YTD, QTD, etc.â
Could also be neat to have an easy way to compare this month, last month, YTD, etc⌠Just like the âPeriod Comparisonâ sheet does it. Iâm not going to pretend I understand how the custom selection works on that sheet, but itâs pretty cool.
@tilleruser@yossiea I updated the template to use the same menu of periods that the Period Comparison sheet uses. Delete your old template and copy again and let me know if you find any problems!
@jpfieber it seems there is date validation on the Description input preventing you from inputting anything. Also it seems the note in there no longer describes that this is a regex and seems to suggest just inputting any text will match your descriptions. This looks great though, thanks!
Thanks! This is a really great tool. I just modified one thing and added a Group column and filter, so I can filter by group or category.
-edit-
What I did to add Group/Category filter is as follows:
First I added a few columns to the sheet, so I can fit in the group/category into the results. This meant columns on the right moved over.
I added two rows in Column T-W and used the Category sheet rather than the transaction sheet to pull cat/group;
Column T is where you should have a list of fields, and W should be the final result, such as Transactions!Amount or similar.
Column T, I put in two rows, one with âCategoryâ and one with âGroupâ.
Column U, =MATCH(T10,INDIRECT("'Categories'!$1:$1"),0)
Column V,=char(64+U10)
Column W=="'Categories'!$"&V10&"2:"&V10
I added a group dropdown, simply copy/paste from the category dropdown.
You should already have a list of Groups, in my case itâs in Column V.
Just add that list to the data validation, replacing the list for Categories in the new Group dropdown.
Then, in the header query for the Category list, I replaced the formula with an if statement, using the Category sheet (the new field you created prior):
Now, the Category drop down list will return all categories, and if you select a Group, then it will filter those categories to match the correct group.
One more bug/featureâŚ
The query for filtering transactions should âupperâ both Col3 and the drop down.
upper(Col2) matches '" & upper(I11)
I missed a few transactions that had different cases.
Also, if you use col2 contains i11, you donât need to use Regex, which might be easier, but youâll then lose the Regex capabilities. Iâm also not sure about speed.
I like what youâre laying down here. I added a âGroupâ dropdown, as well as a group column to each side of the comparison. Now you can compare directly by group, or, as you suggested, you can further break it down by category, with a Category dropdown thatâs limited to that group. Also, had to go back and fix my custom dates, which are hidden down below the filters, only showing up if you choose âCustomâ as the period (not sure if you noticed them hiding down there, might want to check on their health). Thanks for the suggestion and for doing all the heavy lifting on the implementation!
I added this, thanks! For now Iâm thinking the extra power of Regex might be useful for some, but if performance proves to be an issue, that could be revisited.
