In case you missed the announcement: Alteryx One is here, and so is the Spring Release! Learn more about these new and exciting releases here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

How to Build a 24 Month Rolling Window

EnglishManBob
5 - Atom

I have a series of month end dates that I need to turn into a rolling 24 month window.  The date set looks like this:

Dates
1/31/2020
2/29/2020
3/31/2020
4/30/2020
5/31/2020
6/30/2020
7/31/2020
8/31/2020
9/30/2020
10/31/2020
11/30/2020
12/31/2020
1/31/2021
2/28/2021
3/31/2021
4/30/2021
5/31/2021
6/30/2021
7/31/2021
8/31/2021
9/30/2021
10/31/2021
11/30/2021
12/31/2021

 

My solution needs to look like this (easy enough, but see the conditions below):

 

Dates Outcome
1/31/2020 2020-01
2/29/2020 2020-02
3/31/2020 2020-03
4/30/2020 2020-04
5/31/2020 2020-05
6/30/2020 2020-06
7/31/2020 2020-07
8/31/2020 2020-08
9/30/2020 2020-09
10/31/2020 2020-10
11/31/2020 2020-11
12/31/2020 2020-12
1/31/2021 2021-01
2/28/2021 2021-02
3/31/2021 2021-03
4/30/2021 2021-04
5/31/2021 2021-05
6/30/2021 2021-06
7/31/2021 2021-07
8/31/2021 2021-08
9/30/2021 2021-09
10/31/2021 2021-10
11/30/2021 2021-11
12/31/2021 2021-12

 

How do I update the rows so that the last row is always derives from the current data (new months occur on a semi-random basis)? I how do I ensure that the 24 month window only reaches 2 years into the past?

 

So that when January of 1/2022 is added to the data set the result is:

 

Dates Outcome
2/29/2020 2020-02
3/31/2020 2020-03
4/30/2020 2020-04
5/31/2020 2020-05
6/30/2020 2020-06
7/31/2020 2020-07
8/31/2020 2020-08
9/30/2020 2020-09
10/31/2020 2020-10
11/31/2020 2020-11
12/31/2020 2020-12
1/31/2021 2021-01
2/28/2021 2021-02
3/31/2021 2021-03
4/30/2021 2021-04
5/31/2021 2021-05
6/30/2021 2021-06
7/31/2021 2021-07
8/31/2021 2021-08
9/30/2021 2021-09
10/31/2021 2021-10
11/30/2021 2021-11
12/31/2021 2021-12

1/31/2022 2022-01

1 REPLY 1
binuacs
21 - Polaris
Labels
Top Solution Authors