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