We are celebrating the 10-year anniversary of the Alteryx Community! Learn more and join in on the fun here.
Start Free Trial

Alteryx Designer Desktop Discussions

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

How to define rolling 12 month periods?

csh8428
11 - Bolide

I have a dataset that looks like this

KEY_cEFF_DT
2269_1_15/1/2025
2269_1_17/9/2024
2269_1_13/21/2024
  
5523_1_15/28/2025
5523_1_14/29/2025
5523_1_14/19/2024
5523_1_14/17/2024

 

how could I define a 12 month(365 days) rolling period that sequences up for each period where the min EFF_DT for a given KEY_c is the starting point?

 

Results would look like this

KEY_cEFF_DTTwelve Month Period
2269_1_15/1/20252
2269_1_17/9/20241
2269_1_13/21/20241
   
5523_1_15/28/20252
5523_1_14/29/20252
5523_1_14/19/20241
5523_1_14/17/20241
1 REPLY 1
CoG
14 - Magnetar

MVPs for this solution involve the DateTimeDiff() function and the Summarize-Join Tools Design Pattern:

  1. Summarize to identify the minimum [EFF_DT] for each [KEY_c] then join back to original dataset
  2. Compute the DateTimeDiff([Date], [Min_Date], "Year") using "Year" as the time interval, add 1 to reindex
  3. You are good to go!

Screenshot.png

 

Hope this helps and Happy Solving!

Labels
Top Solution Authors