What I’m trying to do
I am creating a financial tracking system where I gather all transactions through a single modal form-driven template. Metadata properties include:
Recurring Transactions (form and template name)
bank_name
income_expense (toggle to identify if the transaction is income or expense)
income_type (only created if income_expense is income)
expense_type (only created if income_expense is expense)
transaction_name
amount
months_charged (only created if income_expense is expense)
months_received (only created if income_expense is income)
I want to create a report that has multiple dataview queries:
- A table showing all expenses for the month (completed)
- A table showing all income for the month (completed)
- The Balance
The problem is the balance. Since the data is entered as positive numbers, I’m stuck as to how to take the total income and subtract the total expenses.
Things I have tried
Here’s what I have now.
Total Income (this works)
TABLE WITHOUT ID IncomeTotal AS “Total Income”
FROM “Financial/Recurring Transactions”
WHERE amount
WHERE contains(income_expense, “income”) AND contains(months_received, “January”)
GROUP BY “”
FLATTEN length(rows.amount) as Count
FLATTEN sum(rows.amount) AS IncomeSubTotal
FLATTEN round(IncomeSubTotal) AS IncomeTotal
Total Expense
TABLE WITHOUT ID ExpenseTotal AS “Total Expenses”
FROM “Financial/Recurring Transactions”
WHERE amount
WHERE contains(income_expense, “expense”) AND contains(months_charged, “January”)
GROUP BY “”
FLATTEN length(rows.amount) as Count
FLATTEN sum(rows.amount) AS ExpenseSubTotal
FLATTEN round(ExpenseSubTotal) AS ExpenseTotal
Balance (not working)
TABLE WITHOUT ID Balance
FROM “Financial/Recurring Transactions”
WHERE amount
WHERE contains(income_expense, “income”) AND contains(months_received, “January”)
GROUP BY “”
FLATTEN length(rows.amount) as Count
FLATTEN sum(rows.amount) AS IncomeSubTotal
FLATTEN round(IncomeSubTotal) AS IncomeTotal
WHERE amount
WHERE contains(income_expense, “expense”) AND contains(months_charged, “January”)
GROUP BY “”
FLATTEN length(rows.amount) as Count
FLATTEN sum(rows.amount) AS ExpenseSubTotal
FLATTEN round(ExpenseSubTotal) AS ExpenseTotal
FLATTEN subtract(IncomeTotal,ExpenseTotal)
I KNOW my logic is flawed on calculating the balance. Any ideas on how to make it work? I’m looking for a rounded value of the income minus the expenses.
Thanks in advance