Counting continuous hours used

Question

I'm looking to see how many times a provider didn't use a continuous 4 hour stretch of time. In the example below Andrew didn't had 8 available hours but didn't use the last 4 so he used 1 session. Bob had 8 hours too but never went 4 continuous hours without seeing a patient.

I used a cross tab tool to get the data in the format below. Data is on vertical axis by provider, date, hour, and patients seen. Thought this would be easier.

I was trying to use a multi row formula but I'm stuck. Any guidance?

ProviderDate89101112131415SessionAndrew1/19/2021111100001Bob1/19/2021111011102

MarqueeCrew · Accepted Answer

@donnellya ,

I'm attaching a workflow that sorts your data (vertical) by PERSON, DATE and Sequential Hour.  It looks behind for the last 3 hours to see if they are all 0 (including current hour).  If so, it sets a value of 1 to a new 4hour flag.  It spans days to see if the string gets broken or continued.  Here's the multi-row formula that I used (setting Num Rows to 3):

IIF(
[Usage] = 0       AND [Row-1:Usage] = 0 AND 
[Row-2:Usage] = 0 AND [Row-3:Usage] = 0
,1,0)

Cheers,

Mark