Im going to try and explain this the best I can but I have been trying for ages and my head is about to fall off…
My app is a Jobsheets App where engineers fill it out when completing a job and then submit a custom form.
The Job Tab has all of the job information and the ones that are important in this case are:
Completed Date
Completed By
Job Price
In my User Sheet, I have a Relation where User Name matches JobTab/Completed By. I then have a rollup of the Job Prices in that relation that calculated the sum of all of the jobs completed. All works fine!
//Total Earned by engineer TODAY
I now want to be able to calculate the sum of all of the jobs completed in a certain time frame.
So in the Relation for Completed by, I need to check if the Completed Date is Todays Date and only have Those jobs in a Rollup.
//Total Earned by engineer THIS WEEK
Then if possible, have another Rollup, same concept as above but if the Completed Date is within This Week.
As Jeff alluded to, there is a new feature coming that will make this sort of thing trivial. But in the meantime:
Create an if-then-else column in your Jobs sheet:
– If Completed Date is within today, then Job Price
Now you can rollup that column through your existing relation
Same thing here, except you’ll need a math column to pre-calculate the date/time at the start of the week, and then compare that to the Completion Date. Assuming that your week starts on Monday, then the following could be used:
Okay, relising things as I go along… I just made a math column and calucated (Start of the Week + 6) and it gave me the Sunday. So now I can add another Case after checking if Completed date is on or after Start of the Week, and check if Completed Date is on or Before End of the Week.
You don’t actually need to do that, unless you have completion dates in the future. And I assume that’s not the case?
The math column should return a date, which represents midnight on the most recent Monday. So any row with a completion date after that date must be in the current week. Therefore, your if-then-else column is simply:
If completion date is after math date, then Job Price.
That will give you a number that looks like 202305 (for May 2023).
Do the same thing for your Date column in the Jobs table, then you can do a direct numerical comparison.
What would be the best way to store these values? When the month changes, the values will go back to 0 until another job is completed so I need a way of storing each day, week, month and years takings.
Okay, so this is where I would use a Helper Table.
Have a read through that thread, and it should give you the general idea. But essentially you’d just need a separate table with 12 rows - one row for each month of the year. Then you build relations from that table and do rollups through those relations. That’s a gross over simplification, but once you get the hang of it you can use this approach to dynamically generate any sort of report for any time period that you need.