Question

Hello!

I have one main database where I input all my expenses and incomes.
Two of such expenses would be inputted in the format:

(date) March / (amount) 20$ / (type) transportation
(date) April / (amount) 30$ / (type) transportation

Then, I want to have another database that Sums expenses by type and by month. So, the sum filtered by march would output be 20$, and april, 30$. Yet, it ads up as 50&.

In my current relation (I am using tables), in the expenses database, the TYPE column is the Relation that outputs to the budget database. In the budget database, each row is one type (say, transportation or groceries). When all the expenses inputs are put in the budget database, they fill the correlated Relation column. If I were to try and filter by date, the filters looks for CONTAINS or IS March or April - depending on the input format. Yet, in both cases, CONTAINS or IS measures the ROW which is TRANSPORTATION, that contains March and April, not differentiating the months.

I do not know how to solve it. Any alternative solution is welcome. I tried to keep the question short, but I can provide extra information if required.

Thank you for your attention and support.

Answer 1

Create a new views for your Notion databases using filters by month.

It seems you are trying simple filters, just click advanced and you will be able to fine tune the filter to do exactly what you need.

Answer 2

You're right that view filters don't change what a rollup sums. A rollup always counts every related row. There are two ways around it.

A. Formula instead of rollup (on the Budget/Type database):

prop("Expenses")
  .filter(formatDate(current.prop("Date"), "MMMM YYYY") == "March 2023")
  .map(current.prop("Amount"))
  .sum()

Swap the hard-coded month for formatDate(now(), "MMMM YYYY") to get "this month".

B. One budget row per type per month. Give Budget rows names like "Transport – Mar 2023" and relate each expense to the matching row. Then a normal rollup Sum is already split by month. You can set the relation automatically with a database automation, or a button that also sets the date.

Option A is less upkeep. Option B makes month-over-month tables easy.

We keep a free freelancer template (clients and projects) here, if useful: https://2rds.github.io/freelancer-ops/lite.html?ref=notionanswers