I’ve been trying to create a report that compares the actual spending by category of the current Year To Date (YTD) with the actual spending for the same categories in the previous year for the same period.
I want to filter the result by:
So far, I haven’t found a way to do this with a Pivot Table or using the Query function.
I can almost get this report by using two queries on the same tab but the categories don’t line up if an amount shows up in one year but not another. If using 2 queries, the list of categories may not be the same if there are amounts for a category in one of the periods but not the other. This makes it difficult to compare the spending by category.
Any suggestions on how to generate this report would be appreciated.
Thanks, Joe.
The Transaction Comparison report lists each individual transaction rather than the sum by category. Were you suggesting that I use it as a starting point?
The report I’m hoping to get would look something like this:
I assume I’d have other cells containing the start and stop dates for the current and previous YTD periods.
So far, I haven’t found a way to show the same list of categories in the same order even if the amount for the category is zero in one of the periods.
Would it help to create a helper column with a list of all the categories used on the Transactions tab and use that list (somehow) to create the comparison I’m looking for?
Thanks again for your reply.
Hi Scott,
I played around with a Pivot Table option and produced a “not so elegant” solution, but it does seem to work accurately.
I added two new columns to my Transactions sheet. A Year Column and a MonthOnly column. This allows me to filter the Pivot Table by this year/last year and by the YTD month, so I only see up to YTD in both years.
I sorted the Year columns in descending order and filtered less than the current OnlyMonth number to get the last full YTD month for both years. (I didn’t use the existing Month column because it makes it too hard to filter out the prior year months that are past the current YTD month.)
Then I made my own “Diff” column next to the Pivot Table and added the difference calculation and dragged it down past the last row.
Finally, I added the Group and Account fields for filtering. I don’t have a Type field in my Transactions sheet so I left that out, but you could easily add that.
Pasting in examples of the end result and the Edit info:
I filtered MonthOnly column for “Less than 3” to get YTD entries up to February in this example.
The zeroes at the end are just extra rows of the difference calculation. They will change as the filters change and the rows increase or decrease. You can delete them or drag the formula to more rows if extra rows get added as the year goes on.
Here are the Pivot Table set up edit entries:
Let me know if I left any explanation out if it looks workable for you.
Fred
1 Like
Thanks, Fred.
This looks like it will work. I’m going to try it tomorrow morning. Your explanation was crystal clear.
Fred:
It worked perfectly. Thanks very much.
ScottC
Saluda, NC
That looks really nice and neat. It might be too late but there is a period comparison tool available that also lists percentage differences. It should be here: Period Comparison Template I’m on Mobile so it might not be exactly it.
1 Like
