- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎Feb 03, 2025 10:22 AM
Hi,
Hoping that someone might be able to help me with a formula question (I'm quite new to formulas and have been fumbling along until now, but have reached the limit of my knowledge!)...
I have a table of payments that have been made, each with the payment date. These Payments are associated back to a project budget and are rolled up so that, at any point, you can see the total Actual spend to date. I've got this all working well.
I am now trying to add in a new field in the Project table which shows total Actual spend as at the most recent quarter end - and I'm stuck! I've managed to write a formula in the Payments table which formats the payment date into the year and quarter (so turning 2022-02-04 into 2022-1) but I now want to work out how to pull this through to the Projects table. I know that I could build a series of fields in the Projects table that rolled up (respectively) all Payments in 2022-1 and then all Payments 2022-2 etc etc, but this doesn't feel very sustainable as I will need to keep adding fields every 3 months. Is there a way to build a formula in the Projects table which works out what the current quarter is, and then pulls in the total actuals as of the end of the previous quarter?
Thanks in advance for any help!
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎Feb 03, 2025 12:29 PM
Here's one approach that might work -
1. In Payments, add a formula to check if the date is in current quarter and format the output as single select options (Yes/No). e.g.
2. In Projects, create a new rollup "Total this Quarter" that only sums the payments where isCurrentQuarter is Yes
3. Add a new column formula in Project that's Total Amount - Total this Quarter to get the total amount as of the end of the prev quarter
if you dont want / need the total this quarter and/or you also have future payments, you could modify the formula in Payments to check all 3 cases (e.g. if payment quarter < current quarter, Past, if =, Current, else Future) too and then just roll up the past payments
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎Feb 03, 2025 04:52 PM
Here is expanded formula, based on idea by @ibayub
You can use it in rollup built-in filter, like one field shown total of current, next - total of previous quarter, etc..
Version to copy
IF(DateField,
IF(DATETIME_FORMAT({DateField},'YYYY-Q')=DATETIME_FORMAT(TODAY(),'YYYY-Q'),'Current Quarter',
IF(DATETIME_FORMAT({DateField},'YYYY-Q')=DATETIME_FORMAT(
DATEADD(TODAY(),-1,'quarter'),'YYYY-Q'),'Previous Quarter',
IF(IS_AFTER((DateField),TODAY()),'Future','Past')
)
),'date absent')