Spotfire - Count consecutive days of downtime

Question

Screen shots are below. One shows my data set. The second shows what I am trying to calculate. I have one record per well per day. There is a downtime column that tells me how many hours a well was down in a give day. I would like to count consecutive days of downtime where downtime = 24. If the calculation encounters a record (in date order) where downtime drops below 24 hours, the calculation should stop and return null and then pick up again counting consecutive days when downtime = 24.

Then I will need to identify the blocks of dates where consecutive days of downtime are 60 days or more so that I can count up the number of wells that were not down in a given month. If a well has 60 days of consecutive downtime that falls across three months, I will remove it from the well count from all three of those months.

Can you try and add the data set screen shot again? I don't see it. Thanks. — scsimon
any chance you could attach a sample data set? I feel like this is going to take a lot of testing — scsimon

TxAggie2007 TxAggie2007 · Accepted Answer · 2016-07-14T14:34:23

Here is a string of calculated columns that when entered in successive order should achieve your desired result. You can combine some of these if you'd like in order that you end up with a fewer total number of columns. Also, I've based these columns off your single well sample data set, but I'm guessing you'll have more than one well so you'll have to incorporate your unique ID into the over statements where necessary.

DowntimeFlag = If([DownHours]=24,1,0)
DowntimeFlagCompare = SN(Avg([DowntimeFlag]) over (Previous([Date])),If([Date]=Min([Date]),if([DowntimeFlag]=1,1,0)))
ResetCounterFlag = If(([DowntimeFlag]<>[DateFlagCompare]) and ([DowntimeFlag]=1),1,If([Date]=Min([Date]),if([DowntimeFlag]=1,1,0),0))
CumResetCounter = Sum([ResetCounterFlag]) over (AllPrevious([Date]))
SpotfireCount = If([DowntimeFlag]=1,Sum([DowntimeFlag]) over (Intersect([CumResetCounter],AllPrevious([Date]))),null)

When these are entered, this is what your data table looks like:

Consecutive Days of Downtime Data Table

Spotfire - Count consecutive days of downtime

1 Answers