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:
Tag 1 … $xx
Tag 2 … $yy
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.
- In a new sheet, specify a category and a date range (just to manage the scope of what is queried… for performance).
- Use a QUERY or FILTER function to get all transactions within that date range with the selected category.
- Use the SPLIT function on all contents of the Tags column to individualize the tags. (I haven’t tried this but you’d need to create a single concatenated string from the range… and there may be an issue with the string size overflowing).
- Run a UNIQUE function against that data to reduce the tags to a list of distinct (non repeating) tags.
- Run a SUMIF function to query for each unique tag.
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
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.
Did any of these suggestions help? If so, please mark one as the solution.
Okay I’m stumped… how can I build in a date range filter to this?
something like what’s on the tags report itself:
I am not great at working with the google sheets query function.
Just trying to do it with a literal start and end date thus:
=QUERY(INDEX(QUERY(SPLIT(FLATTEN(Transactions!B2:B&“|”&Transactions!C2:C&“|”&Transactions!E2:E&“|”&Transactions!F2:F&“|”&SPLIT(Transactions!D2:D,“,”,)),“|”),“WHERE Col5 IS NOT NULL AND Col1 >= DATE ‘2023-05-01’ AND Col1 <= DATE ‘2023-05-30’”)),“SELECT Col5,SUM(Col4) GROUP BY Col5 PIVOT Col3 LABEL Col5 ‘Tag’”)
Error Unable to parse query string for Function QUERY parameter 2: AVG_SUM_ONLY_NUMERIC
The function works without the two AND parameters in the query against the generated records from flatten:
…AND Col1 >= DATE ‘2023-05-01’ AND Col1 <= DATE ‘2023-05-30’"
and if I just put something I know will eval to true (like IS NOT NULL) those params work with the AND keywords…
What am I doing wrong, folks?