How to define rolling 12 months period?
Options
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
csh8428
11 - Bolide
‎09-25-2024
11:47 AM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
I have a data-set that looks like this
KEY_c | EFF_DT |
2269_1_1 | 5/1/2025 |
2269_1_1 | 4/2/2025 |
2269_1_1 | 9/2/2024 |
2269_1_1 | 8/31/2024 |
2269_1_1 | 3/25/2024 |
2269_1_1 | 3/23/2024 |
2269_1_1 | 3/21/2024 |
5523_1_1 | 5/28/2025 |
5523_1_1 | 4/29/2025 |
5523_1_1 | 9/29/2024 |
5523_1_1 | 9/27/2024 |
5523_1_1 | 4/21/2024 |
5523_1_1 | 4/19/2024 |
5523_1_1 | 4/17/2024 |
How would I go about adding a "ROLLING_TWELVE_MONTH_PERIOD"(365 das) where the calc starts at the MIN EFF_DT for each KEY_c so the results look like this
KEY_c | EFF_DT | Twelve Month Period |
2269_1_1 | 5/1/2025 | 2 |
2269_1_1 | 4/2/2025 | 2 |
2269_1_1 | 9/2/2024 | 1 |
2269_1_1 | 8/31/2024 | 1 |
2269_1_1 | 3/25/2024 | 1 |
2269_1_1 | 3/23/2024 | 1 |
2269_1_1 | 3/21/2024 | 1 |
5523_1_1 | 5/28/2025 | 2 |
5523_1_1 | 4/29/2025 | 2 |
5523_1_1 | 9/29/2024 | 1 |
5523_1_1 | 9/27/2024 | 1 |
5523_1_1 | 4/21/2024 | 1 |
5523_1_1 | 4/19/2024 | 1 |
5523_1_1 | 4/17/2024 | 1 |
Labels:
- Labels:
- Help
- Preparation
1 REPLY 1
SPetrie
13 - Pulsar
‎09-26-2024
03:05 PM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Here is one method. You need to get your dates into an actual date format in order to get a time difference. Then You grab the minimum date per key and use it to compare.
I used the ceiling of the difference in days divided by 365 to get the 1 or 2 values and a formula to clean up the 0 values for same dates.
