I have a transactions table with amount and date columns. If the amount > 0, it is an expense, otherwise, its an income.
I want to display a table with income vs expense as a ratio that has a "month and year" variable but I'm wondering what's the best way to do this.
My current attempt:
SELECT
date_trunc('month', date) AS txn_month,
sum(case when amount > 0 then amount else 0 end) as monthly_expense,
abs(sum(case when amount < 0 then amount else 0 end)) as monthly_income
FROM transactions
where transactions
GROUP BY txn_month;
This gives the right result
However, I can't use a month+year filter on this in a dashboard. It says there aren't any valid fields.
