Hi there!
I've been burning my head with this but i can't figure out why the hell is not working as it should.
So, i have a financial database with lots o tables. Specifically to this problem, i have this tables:
- Expense tracker; where i input all my expenses.
- Categories table, where i have all my expense categories, this is linked to the expense tracker.
- Purpose table, which are sub categories of expenses, so it is linked to categories table and to expense tracker.
Each time i input an expense i select a category and a purpose.
Category table view
Lets leave the category table to the side now, because it rolls up data from the purpose table and it only serves to have the category and subcategory structure, everything is happening on the other two tables.
Expenses Table View
Expense Example
The expense tracker table has the following fields (relevant to this matter):
- Date
- Month, formula extracted from date.
- Year, , formula extracted from date.
- U$D, expense amount if in US dollars.
- AR$, Expense amount if in argentinian pesos.
- Consolidated amount, Formula that consolidate the amount spent into dollars, with this formula: "if(empty(prop("U$D"))==true,prop("AR$")/prop("U$D Value"),prop("U$D"))" using an exchange rate from another table to conver AR$ into U$D and have all the expenses in U$D no matter how they where payed.
- Category, linked to category table.
- Purpose, linked to the purpose table.
- One property per each month of the year (called "TY January Amount, ...) that with a formula puts the consolidated amount value if the expense corresponds to that month, and if not it puts 0. This is the formula for january for example: "if(and(prop("Month")=="January",year(now())==prop("Year")),prop("Consolidated Amount"),0)"
Purpose Table View
Purpose Example
The Purpose table also serves as my budgeting table. So i have the following fields in this table:
- Category, linked to category tables.
- Expenses, linked to the expenses tracker table.
- One number property per month of the year, where i input my monthly allowance for the current year for that particular purpose. (Only done in U$D).
- One rollup property per month of the year (called "Spent January", ...), that rolls up from the expense tracker al the expenses corresponding to that specific month. This way i would have in the purpose table all the expenses from that category and subcategory separated by month.
Rollup field example
Now the thing is that that las rollup does not sum, and returns 0 when there are values to sum. But if i select the rollup to show original it shows all the values, as shown here:
Spent January Roll up showing original values
Spent January Roll Up with sum
The funny thing is that if i roll up the "consolidated amount" field instead, it does sum, as shown here:
Rollup adding consolidated amount instead
But this doesn't serve my purpose because it will add all the expenses in that category instead of just the ones from the present year and the corresponding month.
I tried setting all fields to numbers instead of currency, checked i there was anything different between "consolidated amount" field and "spent month" fields, but they are all formulas, that return numbers in currency, and no empty values are left. So if it works in one the it should work for the others.. doesn't it?
I can't understand why it shows the original values correctly but it doesn't add them..
Someone that can illuminate me with this issue???
Thanks a lot!
