How to create a 90 day rolling counter that sums

[eunsuhcho]

Hi,

I need some help.

As you can see in the Sample Data Screenshot.PNG, I'm working to create a rolling 90 day sum counter.

For every student, I need to have a total number of absences for 90 days (so a student should be flagged any time they have more than 4 absences in 90 days. I'm struggling on creating the 90 day counter)

So ideally the end result should be 

Student ID | Number of Absences in 90 day Window

1111 | 1 (since the absences of 9/1/23 and 9/23/23 were passed the 90 day window)

2222 | 2

3333 | 1

I tried many other ways like a Multi Row Formula Tool

if [+90 days] >= [Date of Absence] then [+90 days]

elseif [Row-1:Target Date] >= [Date of Absence] then [Row-1:Target Date] (but it won't derive the Target Date from row previous(?))

else [Date of Absence]

endif

[Prometheus]

@eunsuhcho I created a field called "Min Date," which is the run date of the workflow minus 90 days, using the Formula tool. Then I used a Filter tool after that where the absence date >= Min Date. I used a Summarize tool after that to group by StudentID and sum the absences. Since the formula expression is dynamic, it'll always be at 90 days. 

 

 

 

[eunsuhcho]

@Prometheus Hmm I see that the [Min Date] is always 2023-08-02

This is the output I want

ID          ABSENT DATE       +90 DAYS    

1111       2023-09-01             2023-11-30

1111       2023-09-23             2023-11-30

**** (we move onto the next 90 day phase following absence because it's only a "problem" if they get 4 in one 90 day period)****

1111       2023-12-01             2024-02-29

2222       2023-10-01             2023-12-30

2222       2023-12-23             2023-12-30

3333       2023-12-01            2024-02-29

So then if [+90 DAYS] >= [ABSENT DATE] we would create a new field [Counter] += 1

So the ideal output would be

ID           TOTAL NUMBER OF ABSENCES 

1111         1

2222        2

3333        1

[Prometheus]

@eunsuhcho The minimum date is relative to the day that you run the workflow. If you run it tomorrow, Min Date will be 2023-08-03.