Calculating Relative Quarters at Calendar Quarters
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Labels:
- Date Time
- Time Series
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@terrence10 take a look at this, does it help?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi @terrence10 i have attached the screenshot, hope it helps.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@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)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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"
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi @terrence10 i tried it with a mutli formula tool i hope this will work
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
![](/skins/images/72080B1993C0EC7A53569ADF25905C2F/responsive_peak/images/icon_anonymous_message.png)