Hmmm…
@Brian_Sholis,
I think I’d make the same suggestion to you as I made to @Whitney_Mahoney – you’d do much better to keep all of your expenses in a single table. You are already using a date field, which will help to separate them by month, and you are marking them with categories.
So step 1 is to consolidate all of your expenses into a single table by copy-pasting all of them from the table they are in into the new, unified table (if this makes you nervous, do it on a copy of your base first).
Then, create a field called “Year” that has this formula in it:
IF(Date, YEAR(Date))
Create another field called “Month” that has this formula in it:
IF(Date, DATETIME_FORMAT(Date, 'M') & " " & DATETIME_FORMAT(Date, 'MMMM'))
Next, create a new Grid View - this view will serve as your analysis quick view to see spending by month and by category, so name it accordingly. Use the “Group” menu at the top, and Group first by the “Year” field 9->1, then by the “Month” field 1-9, then by the “Category” field.
What you will see happen is your records will fall under nested groups, like I have mine here:
Your top level group will be the Year, so all your 2018 expenses will be grouped together, and under the year, they will be grouped by Month, and then under that, grouped by category. You will be able to see summaries in the summary bar for each grouping, in nested fashion. In my screenshot, I’m using the Standard Deviation, Average, and Histogram summary functions, but there is a Sum function you can use which will sum up all of your expense Costs first per category within a month (total spending in “Household Items” for “November” of 2018), then per Month within a year (total spending in all “Categories” for “November” of 2018), and then per Year (total spending in all “Categories” for “All Months” of 2018).
You could create another view in the same table that groups by Year and then by Category (skipping Month in-between), which would allow you to see total spending for “Household Items” for all of 2018.
The next step is to relate Income and Expenses in some way, and I think I’d suggest doing this with a join table. So, again, the first step will be to consolidate all Income into a single table, and use a Date field to distinguish them – create the same “Year” and “Month” formula fields there if you’d like to see them Grouped by those periods in the Income table.
I’m going to describe the simplest join table setup, and if you want to expand on it later, you can. The simplest setup is to create an all-to-one relationship between both your Income records and your Expenses records to the join table. Let’s call this third table “Summary”. It will have, literally, only 1 record in it. You can name that record whatever you like – I usually just use a dash “-” or some emoji like “
” – and then you will create a “Link to record” field in both your Expenses table and your Income table that links to the “Summary” table. Back in your “Summary” table, you’ll see corresponding link fields named “Expenses” and “Income”. In your Expenses and Income tables, link every single record to the 1 record you have in your Summary table (this can be done quickly by linking the top record and then dragging the handle to auto-fill the rest).
Back in your Summary table, create a Rollup field that points at the “Income” table, the field that has the income dollar amount, and uses the SUM(values) rollup function.
Then create another Rollup field that points at the “Expenses” table, the “Cost” field, and uses the SUM(values) rollup function.
Now you’ve got your total Income summed up, and your total Expenses summed up – you can create a formula field that uses {Income} - {Expenses} to get your current balance.
now I feel dumb! haha