Greetings Community-
I am having a brain blip…I am attempting to filter a table based on a date for the last 4 quarters (inclusive of current quarter). I am seeing that you can filter for quarters for “during the current”…however everything else is by months.
Any guidance on how to filter for the most recent 4 quarters (inclusive of the current quarter) would be appreciated. @moe ???
Adam
isnt last 4 qtrs same thing as last 12 months. Will this work? the screenshot has 12 months but if you include AND current qtr then perhaps change the 12months to 9 months to avoid risk of spanning across to 5 qtrs.
Hi @Shumon -
The last 12 months isn’t the same as the last 4 quarters since 4 quarters depending on the date when the counting starts.
For example, if you started counting 1 Jan of this year (which is the first quarter in the count then the 4 quarters would stop at April of the previous year (quarter 2)…not Jan of the previous year since that would be 5 quarters.
Disregard all, I have given up on this option and I haven’t gotten any support from the Tada team. #frustrated.
Thanks @Shumon, I will play with this but I don’t think this will work at first glance. Maybe it is a roadmap to a solution for my application. I very much appreciate your comments.
Adam
Did you try the ‘Last 9 months’ plus second criteria ‘Is during current qrt’ ?
Hi Adam,
If not solved yet then try this: Create your own calender table and add fields as shown below. The 4th field (FK_PRID) is a period ID and is a running increment of qtr. This field can be used to return the date range current qtr plus last 3 qtrs. For example 1st qtr of 2023 plus last 3qtrs would be current PRID - 3 (5-3)
| PK_OPID | Date | Qtr | FK_PRID |
|---|---|---|---|
| 1 | 01/01/2022 | 1 | 1 |
| 2 | 02/01/2022 | 1 | 1 |
| 3 | 03/01/2022 | 1 | 1 |
| 4 | 04/01/2022 | 1 | 1 |
| 5 | 05/01/2022 | 1 | 1 |
| 6 | 06/01/2022 | 1 | 1 |
| 7 | 07/01/2022 | 1 | 1 |
| 8 | 08/01/2022 | 1 | 1 |
| PK_OPID | Date | Qtr | FK_PRID |
|---|---|---|---|
| 366 | 01/01/2023 | 1 | 5 |
| 367 | 02/01/2023 | 1 | 5 |
| 368 | 03/01/2023 | 1 | 5 |
| 369 | 04/01/2023 | 1 | 5 |
| 370 | 05/01/2023 | 1 | 5 |
| 371 | 06/01/2023 | 1 | 5 |
| 372 | 07/01/2023 | 1 | 5 |
| 373 | 08/01/2023 | 1 | 5 |
I have messaged you excel table which you can import into tb table. saves you time.
Just my brain fart:
What if you take 2 fields. 1 with current quarter and then one with the previous 3 quarters. And then add these answers in an equation. Is that not what you are looking for?
Hmm…tricky… ideally it looks like “quarters” should really be added to the filter, since, as you say, Quarters are available “during the current” term just not available in the “next” or “previous” 
I guess you could write your own Pipe. I was going to suggest you use the Date Pipes that are already available but annoyingly I think you’d need the ability to Subtract months (9) and then call the “Get Beginning of Quarter” API call but very frustratingly the “Subtract month” API call also isn’t currently available!
Worst case a small lookup table as previously advised might be the only route.
Further to this, I just had a little play and wrote this Pipe that Subtracts 9 months so I think if you were to update this to either include the rest of the calculation or use the existing pipe to set this date to “Set Beginning of Quarter” you could achieve your result.
new Date(new Date({value1}).setMonth(new Date({value1}).getMonth() - 9))
(I think I’d persevere and make a single pipe call if poss)
I played with it a bit more, I think this is the full and final formula so you could stick this in your pipe (and smoke it! 
)
Place this in the Custom Javascript Request:
new Date((new Date(new Date({value1}).setMonth(new Date({value1}).getMonth() - 9))).getFullYear(), 3*(Math.floor(new Date(new Date(new Date({value1}).setMonth(new Date({value1}).getMonth() - 9))).getMonth() / 3 + 1))-3, 1)
If you input any date, this works out the start date of the last 4 quarters inclusive of the quarter you’re already in.
For example, if you entered 1st April 2022 it returns 01/07/2021. You could store this date in the record and then use in the filter.
You need to submit the date in “04-01-22” format.
Please test thoroughly…especially any edge cases. 
If you don’t want to provide a date value it could be updated to just use the Date.now() method to use the current date.
Graham.
Graham, the honourable gentlemen who raised this issue is yet sort out his table relationship so perhaps this challenge can be revisited once we know the db normalisation rules have been followed.
“Stick this in your pipe and smoke it” …lol !!
