Creating a dynamic date range

[rmould]

Hi,

I am trying to create a script which pulls specific data based on the current date/month, and extends the range of data back by two cohorts and forward by two cohorts based on the calendar month:

Cohort	Calendar Month
A	08,09,10
B	11,12,01
C	02,03,04
D	05,06,07

At any one time, I should only see the following:

- Previous Cohort 1

- Previous Cohort 2

- Current Cohort

- Future Cohort 1

- Future Cohort 2

So based on the current date being in February 2020 (Month 02), I should be able to pull data for months relating to Cohort C (Current), data for the previous 6 months (Cohort A and B) and data for the upcoming two cohort months (Cohort D and Cohort A). In theory, I should see data for August 2019 - October 2020. Then come May 2020 when a new cohort begins, everything should shift by one cohort.

Is there an easy way to create this dynamic range based off the current month, if the cohort months are specified in a table anywhere?

Thanks

[afv2688]

Hello @rmould,

Here you can see a way to calculate the dates you asked. You can replace the previous month value (-6) with the one incoming from another field. The same can be done with the 'future' one.

If you give me a clear example (instead of the specified anywhere) I may be able to come up with a better solution

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Regards

[rmould]

Hi @afv2688

Thank you for the below. I have tried running it but I seem to have trouble with the TODATE on v11.7 of Alteryx - assuming this is from a later version which my organisation hasn't upgraded to yet?

Apologies but I am unsure what you mean by clear example. But what I am trying to do and show is as follows based on the current calendar month:

Current:

Based off calendar month being Feb 2020	Based off calendar month being May 2020
Previous Cohort A (August 2019-October 2019)	Previous Cohort B (November 2019-January 2020)
Previous Cohort B (November 2019-January 2020)	Previous Cohort C (February 2020 - April 2020)
Current Cohort C (February 2020 - April 2020)	Current Cohort D (May 2020 - July 2020)
Future Cohort D (May 2020 - July 2020)	Future Cohort A (August 2020-October 2020)
Future Cohort A (August 2020-October 2020)	Future Cohort B (November 2019-January 2020)

So you see when it gets to May and the start of a new cohort/quarter it shifts the cohorts by 1. The issue I have is I can't be literal with the formula and say take the current month and subtract 6 months / add 6 months, as it will miss off months within a cohort if I do so. Apologies if I am making it quite confusing.

Regards

[afv2688]

Hello @rmould,

It is possible to be done, what I would need is to know how you get the data exactly to be able to give you the best suited example for your case.

Regards

[rmould]

Hi @afv2688,

Thank you, I have attached the formula tool which I believe this parameter would feed from.

So the [End_Date_Derived] is based off a date from our record system which is a SQL database/table (unfortunately in this example attached, when I take it out the main script the [end_date_month] shows as '0' instead of 2, it works on my main script). The cohorts are then assigned based off the end_date_month. I believe the script would need to take today's month, and link somehow to the cohort in order for it to know which ones to bring back?

Regards,

[afv2688 · Accepted solution]

Hello @rmould,

Since you are using the previous alteryx version I changed my workflow and used yours too. Le'ts see if it works now

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Regards

[rmould]

Hi @afv2688,

Yes that seems to have worked, thank you for your help on this! Most appreciated.

Regards

[pokhan27]

Hi @afv2688 

what if i have to compare same period comparison. 

for example: 

id	region	Apr-20	May-20	Jun-20	Jul-20	Apr-21	May-21	Jun-21	Jul-20
1	xyz	15000	23000	12000	9000	11000	12000	13000	14000

in above case i am doing qtd comparison. if i am in may 2021 and doing Quarterly YoY comparison i want to have the value as "Q1-2020+Q2-2020+May2020" i don't want entire q3 data. similarly i want to make this comparison dynamically like on june it will be "Q1-2020+Q2-2020+May2020+June2020" and in july it will be "Q1-2020+Q2-2020+Q3-2020"  and so on. can we do that in alteryx.