Hi all,
Apologies for starting a new thread on this subject as I’ve seen it covered already in a few other posts but I’m still stuck!
I have a very simple app that contains a list of expenses and I would like to group these expenses by day, week & month and so far I have setup the following…
I’ve split the entry dates into day/week/month using the formulae suggested in other posts and these have worked well and I can make relationship fields with nowDay/Week/Month. The last bit I’m struggling with is how to get the rolled up total for each period in the thisMonthTotal column.
Eventually I want to normalise the grouped totals into a different table rather than lumping on repeated columns all in one table but that comes later!
thanks for your help in advance.
Rob
Firstly, you are targeting a table directly with your rollup, which means that every row in the table will be used in the rollup. What you should be doing is targeting via a multiple relation.
Secondly, your rollup is targeting an if-then-else column which doesn’t allow you to sum the values. It’s not clear to me what the purpose of the if-then-else column is, but I would expect you should be targeting an amount (number type) column.
Sorry I’m away from my PC for the rest of the day but I’ll look into it this evening.
I was getting the same 1723 amount in weekly and monthly columns. Daily was correctly showing 0.
On another note, what are your thoughts on storing the grouped totals in a separate table?
I often do this. It’s especially useful if you want to show data for multiple periods. For example, 12 months. What you would do is have one row for each month of the year, and then build similar relations and rollups, then present the data in a table collection.
I changed the value of the amount in one of the rows and the rolled up value has updated to 1728 but again it has rolled up all the rows not just the ones caught in the relationship.
yeah I’ll try that next. Have a table for weekly and another for monthly and have a row in the respective tables for each week or month. Would still like to sort out the issue I have when using 1 table though as it’s a learning experience!
Ah I don’t quite understand relationships well though so I’ll have a further read about them.
It looks a lot better now and totals look good but I have an entry in every row regardless of whether that row was in the current month or not. Is that expected?
Yes, that’s fine.
It’s because you’re matching from the current week/year/month (which is correct), and those columns have the same value in every row. So every row is finding matches.
