Hello,
I have a sample data set below. I want to assign a quarter relative to a starting date (asofdate) such that the quarters fall on calendar quarters. Two examples below:
Dates 2/29 and 3/31 both fall within the 1st relative calendar quarter from 1/31/2024.
AsOfDate_Out | SimDate_Out | Relative Quarter |
1/31/2024 | 2/29/2024 | 1 |
1/31/2024 | 3/31/2024 | 1 |
1/31/2024 | 4/30/2024 | 2 |
1/31/2024 | 5/31/2024 | 2 |
1/31/2024 | 6/30/2024 | 2 |
1/31/2024 | 7/31/2024 | 3 |
1/31/2024 | 8/31/2024 | 3 |
1/31/2024 | 9/30/2024 | 3 |
1/31/2024 | 10/31/2024 | 4 |
1/31/2024 | 11/30/2024 | 4 |
1/31/2024 | 12/31/2024 | 4 |
1/31/2024 | 1/31/2025 | 5 |
Only 6/30 falls within 1 relative calendar quarter from 5/30. 7/31 is the next relative calendar quarter from 5/31.
AsOfDate_Out | SimDate_Out | Relative Quarter |
5/31/2024 | 6/30/2024 | 1 |
5/31/2024 | 7/31/2024 | 2 |
5/31/2024 | 8/31/2024 | 2 |
5/31/2024 | 9/30/2024 | 2 |
5/31/2024 | 10/31/2024 | 3 |
5/31/2024 | 11/30/2024 | 3 |
5/31/2024 | 12/31/2024 | 3 |
5/31/2024 | 1/31/2025 | 4 |
Last example where as of date starts on a calendar quarter end:
AsOfDate_Out | SimDate_Out | Relative Quarter |
12/31/2023 | 1/31/2024 | 1 |
12/31/2023 | 2/29/2024 | 1 |
12/31/2023 | 3/31/2024 | 1 |
12/31/2023 | 4/30/2024 | 2 |
Any tips on how I can generate the Relative Calendar Quarter by inputting the AsOfDate and SimDate?
Thanks!
Solved! Go to Solution.
Thank you @Manoj_k
It looks like your formula is still identifying what quarter a sim date is vs. relative to the start date:
I see that you assign a quarter to a row - and then use that as part of your multi-row formula.
In the case of an as of date of 5/31 - the calculation will assign the first q correctly - but the next quarter jumps to 3 instead of 2
--Yes, 7/31 is in the 3rd Quarter -- but it's within the 2nd calendar quarter relative to 5/31. I would want a relative quarter of 2 for 7/31.
I think you are looking for absolute dates for quarter - and you are looking at day +1 from your start column. Otherwise you would count 12/31, 1/31, 2/28(29), 3/31 - IE 2. If you are skipping the initial date - you are doing day +1:
I'm sure someone else can help you more - if this doesn't get you where you want but:
1) create a new field called adjusteddate:
datetimeadd([AsOfDate_Out],1,"day")
2) create a new field called test
datetimeparse(datetimeformat([adjusteddate],"%Y")+"-0"+
tostring(
(floor(tonumber(datetimeformat([adjusteddate],"%m"))/3)*3)+1)
+"-01","%Y-%m-%d")
3) question (my solution field)
ceil((abs((datetimediff(datetimetrim([test],"firstofmonth"),[SimDate_Out],"month")))+1)/3)
Here are the steps that I went through to match your requirements:
1) I add one day to prevent 12/31/2023 form being flagged (or 6/30 etc...)
2) I create an absolute nearest quarter begin using some convoluted math and a floor function.
3) I now have a new creation date which I plug into my original formula.
This matched what you requested.
Thank you so much for your help! It's so close! Argh --
The RelQtr isn't calculating correctly in the scenario of 5/31 and 7/31 sim date (should be relative quarter 2).
Seems like when AsOfDate = 5/31, the "test" field for nearest quarter should be 4/1 instead of 7/1 -- then the datetimediff calculation should work properly
The other 2 conditions seem to work!
You are correct! I forgot to take 1 away from my month when using my floor division:
You would see the same issue with an August start date I believe!
and to prevent the October situation:
datetimeparse(datetimeformat([adjusteddate],"%Y")+"-"+
padleft
(tostring(
(floor(
(tonumber(datetimeformat([adjusteddate],"%m"))-1)
/3)*3)+1)
,2,"0")
+"-01","%Y-%m-%d")
Awesome! Thank you @apathetichell ! Looks like this is calculating the relative calendar quarters correctly!
I'll have to break down how you calculated that nearest quarter parse/pad/floor logic - very cool solution!
Thanks again!
Let me break this down:
and to prevent the October situation:
datetimeparse(datetimeformat([adjusteddate],"%Y")+"-"+
padleft
(tostring(
(floor(
(tonumber(datetimeformat([adjusteddate],"%m"))-1)
/3)*3)+1)
,2,"0")
+"-01","%Y-%m-%d")
You have a situation where you are looking for the first day of the quarter of day+1 (the adjusted date). you know that quarters start on 1/4/7/10 (ie 3x+1 - where x is 0,1,2,3). To derive which quarter you want you subtract 1 from the current month - and floor divide by 3 (giving you a number between 0 and 3, and making sure you increment at months 4,7,10 vs 3,6,9,12). You now have your date. That's the o.k. part.
Your next challenge is parsing that into a date. you can use the year of your adjusted date (which may differ from your original date's year) and the month and day 01. To get the month into a normal month format you add "-0" and the month.... and that works for January, April and July - for October you get a month of -010. To fix this you use padleft to make sure you have two digits for month and add the 0 if needed.