Aggregation problem with intervals

[anseglko]

I need to find the average interval of two timestamps and filter out some outliers at the same time.

My data looks like this:

Id	Timestamp	Equipment
1	22/07/19 10:02:10	1
2	22/07/19 10:02:50	1
3	22/07/19 10:03:05	2
4	22/07/19 10:03:30	2
5	22/07/19 10:03:46	1
6	22/07/19 19:15:11	1
7	22/07/19 19:16:54	1
	...

I need the interval between two timestamps, grouped by the equipment. This one works easily with:

=avg(aggr(below(Timestamp)-Timestamp,Equipment,Timestamp))

I want this to return something like 40 seconds. However as you can see in ID 5->6 there is a 9 hour jump (this is normal in my data, for example the equipment isn't used during the night, so the jump becomes ~9 hours). So the average gets shifted up a lot. I want to filter those out, for example don't include the time interval to the average if it's over 1 hour (this would be the best, I thought filtering out the nights too, but sometimes the big jumps might happen during the day too).

So how would the filtering these out go? I've already tried stuff like this (0.042 is 1 hour in duration):

avg(if(
aggr(below(Timestamp)-Timestamp,Equipment,Timestamp)<0.042,
aggr(below(Timestamp)-Timestamp,Equipment,Timestamp)))

 But it doesn't work. It sometimes returns negative values, I don't have any idea why.

Ideas what I could try?

[sunny_talwar · Accepted solution]

It should not make a difference in the result, but can you try this

Avg(Aggr(If(Below(Timestamp) - Timestamp < 0.042, Below(Timestamp) - Timestamp), Equipment, (Timestamp, (NUMERIC))))

If this still doesn't work, try to troubleshoot this by creating a chart with Equipment and Timestamp as Dimension and use this as expressions

1)  Aggr(Below(Timestamp) - Timestamp, Equipment, (Timestamp, (NUMERIC)))

2) Aggr(If(Below(Timestamp) - Timestamp < 0.042, Below(Timestamp) - Timestamp), Equipment, (Timestamp, (NUMERIC)))

and see if the row level information looks good or not.

[anseglko]

Hello Sunny,

Thank you! That worked, I get the correct times now without weird negative values.