# New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Our May 2021 end-to-end product release from Data Integration to Data Analytics is out! READ DETAILS
cancel
Showing results for
Did you mean:
Creator

## Aggregation problem with intervals

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?

Labels (10)

• ### Timestamp

1 Solution

Accepted Solutions
MVP

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.

2 Replies
MVP

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.

Creator
Author

Hello Sunny,

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