Generate Prior quarter dates

[Mario36]

Hi All,

Suppose I have a date as 2022-12-31. I would want the below data. Kindly help.

Quarter Date	Quarter	Quarter Start Date	Year	Year Start
2022-12-31	Q4 2022	2022-10-01	2022	2022-01-01
2022-09-30	Q3 2022	2022-07-01	2022	2022-01-01
2022-06-30	Q2 2022	2022-04-01	2022	2022-01-01
2022-03-31	Q1 2022	2022-01-01	2022	2022-01-01
2021-12-31	Q4 2021	2021-10-01	2021	2021-01-01
2021-09-30	Q3 2021	2021-07-01	2021	2021-01-01

[DataNath]

Hey @Mario36, I built a macro a good while ago that got me most of the way there - just needed to add a couple modifications & the Year field!

https://community.alteryx.com/t5/Community-Gallery/Enhanced-DateTimeTrim/ta-p/972535

I've attached a packaged workflow that contains the macro - feel free to open it up and see the calculations etc behind the various dates on offer.

[Mario36]

Hi @DataNath ,

Thank you. but what I was looking for was to generate rows from just a date such as  2022-12-31

[DataNath · Accepted solution]

Apologies @Mario36, didn't mention actually generating all of the rows. If you start with a single date like this:

You can constantly generate one quarter ago like so, where you would just replace '2021-09-30' with however far back you want to go:

The only thing with DateTimeAdd() is that - when using 'month' - this isn't handled in the way we like to think about months i.e. if we're on the 30th and tell Alteryx to go back a month, if the month prior has 31 days, it won't automatically jump to that as the 'end of the month', it'll just literally take away one month to the 30th. Therefore, we can standardise everything to the month end with DateTimeTrim():

The rest of the workflow is then the same as outlined above.

[Mario36]

You are a life saver. Thank You so much

[Q1]

Hey I have a similar project im working on but I need to be able to automate the generation of dates  the past 7 quarter ends, and future dates, how would I do that ?