Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

anseglko
New Contributor III

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:

IdTimestampEquipment
122/07/19 10:02:101
222/07/19 10:02:501
322/07/19 10:03:052
422/07/19 10:03:302
522/07/19 10:03:461
622/07/19 19:15:111
722/07/19 19:16:541
 ... 

 

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?

1 Solution

Accepted Solutions

Re: Aggregation problem with intervals

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

Re: Aggregation problem with intervals

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
New Contributor III

Re: Aggregation problem with intervals

Hello Sunny,

 

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