Calculating Relative Quarters at Calendar Quarters

[terrence10]

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!

[terrence10]

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.

[apathetichell]

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. 

[terrence10]

Hi @apathetichell 

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!

[apathetichell · Accepted solution]

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")

[terrence10]

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!

[apathetichell]

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.