Hi folks,
I’m trying to create a sheet that does custom analysis of budgets vs. actuals over time. The data I need to do so can be found in solutions like Monthly Budget / Monthly Analysis / Yearly Budget / etc., but not in a “clean” format that is easy to work with (i.e. the data is more oriented towards displaying data, not using it as source data for charts & tables). Ideally, I would like a sheet that has the following columns:
Hi @jeff.bordogna,
I believe what you are looking for is called “Unpivoted” or “Tall” data, unlike the “Wide” data in the Categories sheet for the budget months.
A sheet like this is more ‘database’-like rather than ‘spreadsheet’-like and can make it easier to perform some data analysis.
@benlcollins put together this article on the concept:
Doing this by using formulas is not simple.
Tiller doesn’t have an existing sheet with such a data structure.
I recently wrote a Google Script for my own purposes that does this for my Tiller Budget data. It’s not production worthy to work on any Tiller spreadsheet so I’m hesitant to share it, but the concept is:
Read the Categories sheet and calculate the first and last budget month columns
Loop thru each Category Row and inside that loop create another loop that loops thru all the budget month columns
If there is a budget value for the Category and Month, then create a new row in a result array.
After looping over all the Categories, write the result array into a new sheet.
I created a Menu in the sheet to give me the option to run the script, which I do if I update any budget values. At the start of the script, the new sheet’s contents are cleared before the new values are written in.
This script produces an output with the following columns:
Category, Month/Year, Budget
To get the Group column, you could do a lookup of the Category column.
To get the Actual column, you could do a SUMIF() on the Transactions sheet.
Hey @jono - this was very helpful, thank you! I’ve started playing with the concept and will write back when I have it further along.
In the meantime, @TillerTeam, having this type of sheet as a solution seems quite useful as a way to empower custom exploration by users. I see this exploration as a key advantage of Tiller over other rigid solutions like Mint.
… er, data… you’re looking for…