BusinessObjects Board

Calculating average time difference between events

building reports webintelligence legacy
#1

Looking for assistance building a report. First I need the standard deviation / variance of the difference between event times

They look something like this…

SITE ID D&T
WAD1 01/01/2016 00:27
WAD1 01/01/2016 02:38
WAD1 01/01/2016 02:56
…
…
WAD1 07/20/2016 23:49
WAD1 07/21/2016 00:35

I want the average, min & max between those times.

Then I can set up another report that runs every few hours. If an event hasn’t occurred within X amount of time, it will flag and send me a notification. I was thinking I would insert an object for current D&T, and calculate the difference between that time and the last event.

This is to help our team get early notification when our equipment isn’t working, so we can dispatch a technician more timely. Any ideas on the top portion?

Thanks!
Matt[/u][/b]

Mstashek (BOB member since 2016-07-23)

#2

Hi Matt,

I’m not sure what you’re trying to do.

If you have a date and time field, what are you comparing it to?

#3

I’m trying to compare each entry to the entry either above or below it. I would like the time difference between entries.

Occasionally when the equipment stops working, there will be a time gap between entries anywhere from hours to days. I need the time difference between entries for about a year so that I have enough data to put into statistics.

With that I should be able to determine the standard deviation, identify outlying data points, and come up with a number to plug into a new report. That new report would run, say, every three hours. If outlying data is found, it flags and sends me a notification that our equipment isn’t working.

So long story short… I’d like the time difference between:

row 2 minus row one = 1 hr 17 min
row 3 minus row two = 0 hr 18 min
row 4 minus row 3 = 2 hr 37 min
etc. etc.

I’d like two do this for about two dozen different sites… The most data being pulled should have somewhere around 10,000 rows and the least data maybe about 2,000 rows.

I could probably do this easier in Excel, but then I would have to individually calculate and write formulas for two dozen reports…

Sorry for such a long response… any thoughts?

Mstashek (BOB member since 2016-07-23)

#4

Have a look at the Webi function Previous()

Personally, I’d be looking at changing the data model to get a view or derived table build to get an easier data set to work with. Building variances and standard deviations on calculations that span rows isn’t easy.