DateDiff Based on formula

[texxavery]

I am writing a report to calculate server downtime based on monitor data.

I have one formula that identifies the error window by marking the first error and then the first good response.

So report looks like this

Time Difference Test
0:04 good WebTest Verify 30 seconds
0:09 good WebTest Verify 30 seconds
0:14 error WebTest Verify 30 seconds error
0:19 error WebTest Verify 30 seconds
0:24 good WebTest Verify 30 seconds error
0:29 good WebTest Verify 30 seconds
0:34 good WebTest Verify 30 seconds
0:39 good WebTest Verify 30 seconds
0:44 good WebTest Verify 30 seconds
0:49 good WebTest Verify 30 seconds
0:54 good WebTest Verify 30 seconds
0:59 good WebTest Verify 30 seconds
1:04 good WebTest Verify 30 seconds
1:09 good WebTest Verify 30 seconds
1:14 good WebTest Verify 30 seconds
1:19 good WebTest Verify 30 seconds
1:24 good WebTest Verify 30 seconds
1:29 good WebTest Verify 30 seconds
1:34 good WebTest Verify 30 seconds
1:39 good WebTest Verify 30 seconds
1:44 good WebTest Verify 30 seconds
1:49 good WebTest Verify 30 seconds
1:54 good WebTest Verify 30 seconds
1:59 good WebTest Verify 30 seconds
2:04 good WebTest Verify 30 seconds
2:09 good WebTest Verify 30 seconds
2:14 good WebTest Verify 30 seconds
2:19 good WebTest Verify 30 seconds
2:24 good WebTest Verify 30 seconds
2:29 good WebTest Verify 30 seconds
2:34 good WebTest Verify 30 seconds
2:39 good WebTest Verify 30 seconds
2:44 good WebTest Verify 30 seconds
2:49 good WebTest Verify 30 seconds
2:54 good WebTest Verify 30 seconds
2:59 good WebTest Verify 30 seconds
3:04 good WebTest Verify 30 seconds
3:09 good WebTest Verify 30 seconds
3:14 good WebTest Verify 30 seconds
3:19 good WebTest Verify 30 seconds
3:24 good WebTest Verify 30 seconds
3:29 good WebTest Verify 30 seconds
3:34 good WebTest Verify 30 seconds
3:39 good WebTest Verify 30 seconds
3:44 good WebTest Verify 30 seconds
3:49 good WebTest Verify 30 seconds
3:54 good WebTest Verify 30 seconds
3:59 good WebTest Verify 30 seconds
4:04 good WebTest Verify 30 seconds
4:09 good WebTest Verify 30 seconds
4:14 good WebTest Verify 30 seconds
4:19 good WebTest Verify 30 seconds
4:24 good WebTest Verify 30 seconds
4:29 good WebTest Verify 30 seconds
4:34 good WebTest Verify 30 seconds
4:39 good WebTest Verify 30 seconds
4:44 good WebTest Verify 30 seconds
4:49 good WebTest Verify 30 seconds
4:54 good WebTest Verify 30 seconds
4:59 good WebTest Verify 30 seconds
5:04 good WebTest Verify 30 seconds
5:09 good WebTest Verify 30 seconds
5:14 good WebTest Verify 30 seconds
5:19 good WebTest Verify 30 seconds
5:24 good WebTest Verify 30 seconds
5:29 good WebTest Verify 30 seconds
5:34 good WebTest Verify 30 seconds
5:39 good WebTest Verify 30 seconds
5:44 good WebTest Verify 30 seconds
5:49 good WebTest Verify 30 seconds
5:54 good WebTest Verify 30 seconds
5:59 good WebTest Verify 30 seconds
6:04 good WebTest Verify 30 seconds
6:09 good WebTest Verify 30 seconds
6:14 good WebTest Verify 30 seconds
6:19 good WebTest Verify 30 seconds
6:24 good WebTest Verify 30 seconds
6:29 good WebTest Verify 30 seconds
6:34 good WebTest Verify 30 seconds
6:39 good WebTest Verify 30 seconds
6:44 good WebTest Verify 30 seconds
6:49 good WebTest Verify 30 seconds
6:54 good WebTest Verify 30 seconds
6:59 good WebTest Verify 30 seconds
7:04 good WebTest Verify 30 seconds
7:09 good WebTest Verify 30 seconds
7:14 good WebTest Verify 30 seconds
7:19 good WebTest Verify 30 seconds
7:24 good WebTest Verify 30 seconds
7:29 good WebTest Verify 30 seconds
7:34 good WebTest Verify 30 seconds
7:39 good WebTest Verify 30 seconds
7:44 good WebTest Verify 30 seconds
7:49 good WebTest Verify 30 seconds
7:54 good WebTest Verify 30 seconds
7:59 good WebTest Verify 30 seconds
8:04 good WebTest Verify 30 seconds
8:09 good WebTest Verify 30 seconds
8:14 good WebTest Verify 30 seconds
8:19 good WebTest Verify 30 seconds
8:23 good WebTest Verify 30 seconds
8:28 error WebTest Verify 30 seconds error
8:29 error WebTest Verify 30 seconds
8:29 error WebTest Verify 30 seconds
8:30 error WebTest Verify 30 seconds
8:30 good WebTest Verify 30 seconds error

I am trying to calculate the datediff "s" for each error window. I have been able to calculate the time on the previous record but not for time where previous record is = to "error" or something like that.

 

[skuhlman]

You didn't say how you were getting the previous time, but you can probably apply the same principal with an IF
instead of:

fmlPreviousTime = TimeOfPreviousRecord

use a CONDITIONAL IF:

fmlPreviousTime = iif(StatusOfPreviousRecord='Error',TimeOfPreviousRecord,fmlPreviousTime)

 

[synapsevampire]

A bit vague for me.

Are you trying to calculate the difference between the previous rows time and the current rows time?

There's a previous() function in CR that would be fine for this:

if onfirstrecord then
0
else
datediff("s",previous({MyTable.time},{MyTable.time})

Would return the difference in seconds for each row.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com