Hi all, I have installed the Tags Report sheet and find it really useful, but what I would like to do is to see a breakdown of tags filtered to a category or list of categories:
Category Header
Tag 1 … $xx
Tag 2 … $yy
etc.
Anyone know how to do this? Thanks!
There is this report, but I guess it kind of works in reverse of what you’re asking for, @jaeson.paul.
I can envision a set of operations that would get you this data.
That’s how I’d work up to it.
Hope this flow helps.
On my way out the door, but just some quick napkin math without too much thought… Would a query to this effect work (for a single category)?
SELECT Tag, SUM(Amount) FROM Transactions WHERE Category=<selected category> GROUP BY Tag
It could be expanded to a tabular form for all categories as such.
SELECT Category, Tag, SUM(Amount) FROM Transactions GROUP BY Category, Tag
From there it might be massagable into the category being a column header with the tag and summed amount below via a VLOOKUP or XLOOKUP.
Selecting a subset of categories is tricky for the reasons @randy mentioned, but it might be doable in a performant fashion with enough effort.
I like your approach here, @cculber2, but I don’t think this works if there are multiple tags in a single cell (e.g. “Business,Taxes”).
Ahh, good point, @randy. I hadn’t considered multiple tags in a transaction…
After thinking it over more, I have a basic functioning formula that will create a table of summed Amounts for one Tag per row and Category per column. This works with the Foundation sheet with a Tags column added between Categorized Date and Date Added. I still need to replace the direct column references with INDIRECT lookups. A WHERE clause for Category MATCHES and Tag MATCHES can easily be added, but I still need to come up with an efficient way to do a multi-select of categories and tags aside from a long list of data validation drop-downs. There are some columns I can also trim from the inner query. This is a start at least.
=QUERY(INDEX(QUERY(SPLIT(FLATTEN(Transactions!B2:B&"|"&Transactions!C2:C&"|"&Transactions!E2:E&"|"&Transactions!F2:F&"|"&SPLIT(Transactions!Q2:Q,",",)),"|"),"WHERE Col5 IS NOT NULL")),"SELECT Col5,SUM(Col4) GROUP BY Col5 PIVOT Col3 LABEL Col5 'Tag'")
This formula effectively splits single transactions with multiple tags into multiple transactions with a single tag and then queries that virtualized transaction table with a pivot to get the desired result. Theoretically it will work with any number of tags and categories.
Foundation Transactions with sample data:
QUERY with PIVOT on Category, one Tag per row:
thanks for the great suggestions! I will have to skill up a bit to implement these but that’s the fun part…
Update, got it to work, totally great, Thank you!
Now I will try to see if I can put some params at the top to make the report more usable… filter by date range, etc.
, 