Year over year templates

Conversation Ask Anything
1

Are there existing templates or workflows to harvest high level annual data and compile it into a multi-year summary? Something like the Live Profit & Loss sheet but where every column represents a year, and the groups but not the categories are show for simplicity?

3 Likes
2

Hi @Caroleen there are a few options that come to mind:

I think those are all at the category level.

Another option would be to create a pivot table. If you add both the Group and Year columns to your Transactions sheet you could use Group instead of Category and then Year instead of Month.

3

Just gonna chime in here and agree :slight_smile: the suggestions were great, but don’t necessarily get to the nugget of what’s desired.

Maybe a little closer, e.g.:

image
image1665×828 87.5 KB

image

4

Hi Caroleen,

I realize your request is from 3 years ago but I thought I’d reply for anyone else who is reading, assuming you found a workaround.

I needed a multi-year report based on category. I achieved this by creating a new sheet and using a query function. I had it exclude things I didn’t care about like Transfers, Reimbursement, Credit Card Payment and Investments b/c these items would skew the numbers for me.

=QUERY(QUERY({Transactions!B2:B, Transactions!D2:D, Transactions!E2:E, ARRAYFORMULA(YEAR(Transactions!B2:B))}, “SELECT Col2, SUM(Col3) WHERE Col3 IS NOT NULL AND Col2 IS NOT NULL AND Col2 <> ‘Transfer’ AND Col2 <> ‘Reimbursement’ AND Col2 <> ‘Credit Card Payment’ AND Col2 <> ‘Investments’ AND Col2 <> ‘Income’ GROUP BY Col2 PIVOT Col4”, 0), “ORDER BY Col2”)

To show a multi-year view of expenses by category, I modified the query statement to get the groups from the Categories sheet:

=QUERY({Transactions!B2:B, ARRAYFORMULA(IFERROR(VLOOKUP(Transactions!D2:D, Categories!A2:B, 2, FALSE),“”)), Transactions!E2:E, ARRAYFORMULA(YEAR(Transactions!B2:B))}, “SELECT Col2, SUM(Col3) WHERE Col3 IS NOT NULL AND Col2 IS NOT NULL GROUP BY Col2 PIVOT Col4”, 0)

5

Should this query work by default against the Foundation Template? I’m getting an error and would really like to have year over year comparisons (for multiple years).

1 Like
6

@Caroleen and @staceyb

Not sure if this is what you;re looking for, but I created this template to look at multiple years…

image
image1404×650 177 KB

1 Like