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!

[aatalai]

@terrence10 take a look at this, does it help?

[Manoj_k]

Hi @terrence10 i have attached the screenshot, hope it helps.  

[terrence10]

Thanks for the reply @Manoj_k !

Looks like your relative quarter just identifies the calendar quarter the month is in -- I would like it to calculate the calendar quarters relative to the as of date.

Also - the relative quarter can be higher than 4 if the simdate is > 4 calendar quarters away.

[terrence10]

Thank you @aatalai 

Looking at your workflow -- I'm getting an error with the CEIL expression - wrong number of parameters. Looks like I might be on an older version of Alteryx that doesn't have this function?

Formula for reference:

ceil((DateTimeMonth([SimDate_Out])-DateTimeMonth([AsOfDate_Out]) +12*(DateTimeYear([SimDate_Out])-DateTimeYear([AsOfDate_Out]))+1)/3,1)

I'm looking through with your date diff calcs to see if this could work.   

[apathetichell]

@terrence10 - that's not you - the formula is wrong. Ceil takes one parameter.

How about ceil((abs((datetimediff(datetimetrim([AsOfDate_Out],"firstofmonth"),[SimDate_Out],"month")))+1)/3)

[terrence10]

Thanks for the formula @apathetichell 

It looks like the formula works for one condition - when As of Date is 1/31 and there are two months until the next calendar quarter.

However, - the calculation breaks down when there is one or three months until the next calendar quarter. Screenshot of Relative Quarter Calc vs. Expected:

Is there logic like "starting at asofdate, find the nearest calendar quarter end month - Sim dates <= that date are in the 1st relative quarter. Else Increment relative quarters off the "nearest calendar quarter end month" 

[apathetichell]

Can you expand upon this? I'm having an issue reconcilling how 12-31-2023 to 03-31-2024 is one quarter. 

Are you looking at absolute quarters or relative quarters? Are you looking at 120 day sample sizes? Relative quarters would mean quarters away from your column A - and f you look at relative quarters - May->July is 1 quarter. 1 is correct. May. June. July is one quarter. December-> March is two quarters. Two is correct. 

If you want an absolute adjustment to use a March/June/September/December quarter end - I can set that up - but can you clarify what you want? I matched your initial output.

For an absolute quarter you would look at at an April 1st date for an end of May date to look at quarters. Your December -> March compare would still be 2.

[Manoj_k]

Hi @terrence10 i tried it with a mutli formula tool i hope this will work 

[terrence10]

Hi @apathetichell 

I believe I am looking for an absolute adjustment - defining March / June / September / December Quarter end. So, it won't always be 120 day sample sizes. 

In the 2024/05/31 example, the next calendar quarter is 6/30. I want to count how many dates are "within" the next calendar quarter. In this example, only 6/30 is within the next calendar quarter - so relative quarter is 1. At 7/31, 8/31, 9/30 -- these are within the next calendar quarter, so relative quarter is 2

In the 2023/12/31 example, the next calendar quarter is 3/31. Dates 1/31. 2/29. 3/31 are within the next calendar quarter end -- relative quarter is 1. 

Hopefully this makes sense! Essentially want to be able to group by calendar quarters relative to a start date instead of strictly rolling quarters of 120 days.