Calculating Relative Quarters at Calendar Quarters

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!

Date Time

Time Series

Accepted answers

apathetichell

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

All comments

aatalai

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

Calculating Relative Quarters at Calendar Quarters.yxmd

Manoj_k

Hi @terrence10 i have attached the screenshot, hope it helps. Screenshot 2024-04-11 144453.png

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.

Ceil Expression.PNG

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"

Date Calc.PNG

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 Screenshot 2024-04-12 011420.png

Quarterflag.yxmd

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.

Date Calc.PNG

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.

Date Calc.PNG

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!

Date Calc.PNG

apathetichell

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.

Quick Links

This months top contributors

LanisC 335

hhaigh1 280

erich_shiino 280

FláviaB 262

JORGE4900 235