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 |
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.
User | Count |
---|---|
18 | |
14 | |
10 | |
6 | |
6 |