4 Replies Latest reply: Oct 17, 2011 8:10 AM by Dror Svartzman

# Average of the last X periods

Hi,

I'm trying to figure out a way to calculate, in the script, the average time for the last X Iterations. In that case, Iteration = periods.

If X is 4 iterations, then the results should be as follow:

th0, t1 = 7:34

th0, t2 = 7:43

See script below

```source:
LOAD * INLINE [
ThreadID, TaskId, Timestamp
th0, t1, 17-10-11 7:37
th0, t1, 18-10-11 7:38
th0, t1, 19-10-11 7:42
th0, t1, 20-10-11 7:39
th0, t1, 21-10-11 7:32
th0, t1, 22-10-11 7:37
th0, t1, 23-10-11 7:31
th0, t2, 17-10-2011 7:40
th0, t2, 18-10-2011 7:52
th0, t2, 19-10-2011 7:41
th0, t2, 20-10-2011 7:39
th0, t2, 21-10-2011 7:40
];

AvgTimeTaskId:
noconcatenate
LOAD ThreadID,
TaskId,
Time(Average(frac(Timestamp))) AS Avg(Time)
RESIDENT source
[for the last X iterations]
```
• ###### Average of the last X periods

Hi,

This your solution:

source:

LOAD * INLINE [

ThreadID, TaskId, Timestamp, Time

th0, t1, 17-10-2011 ,7:37

th0, t1, 18-10-2011 ,7:38

th0, t1, 19-10-2011 ,7:42

th0, t1, 20-10-2011 ,7:39

th0, t1, 21-10-2011 ,7:32

th0, t1, 22-10-2011 ,7:37

th0, t1, 23-10-2011 ,7:31

th0, t2, 17-10-2011 ,7:40

th0, t2, 18-10-2011 ,7:52

th0, t2, 19-10-2011 ,7:41

th0, t2, 20-10-2011 ,7:39

th0, t2, 21-10-2011 ,7:40

];

AvgTimeTaskId:

load

*,

floor(AVGTime/60)&':'&round(frac(AVGTime/60)*60)          as Time

;

load

ThreadID,

TaskId,

avg(if(len(Time)=5,(left(Time,2)*60) + right(Time,2),(left(Time,1)*60) + right(Time,2))) as AVGTime

resident source

group by ThreadID,TaskId;

drop table source;

Succes!

Halmar

• ###### Average of the last X periods

Halmar,

Appericiate your prompt response.

The code calculates the average for the hole subset. In my scenario i require to produce only the last X rows.

In this case:

for  th0, t2

th0, t2, 18-10-2011 ,7:52

th0, t2, 19-10-2011 ,7:41

th0, t2, 20-10-2011 ,7:39

th0, t2, 21-10-2011 ,7:40

( 7:43)

for th0,t1

th0, t1, 20-10-2011 ,7:39

th0, t1, 21-10-2011 ,7:32

th0, t1, 22-10-2011 ,7:37

th0, t1, 23-10-2011 ,7:31

( 7:34)

• ###### Average of the last X periods

Hi,

I think you could introduce a rownumber for each partition and then use a where-clause in your final avg-calculation load, like:

source:

LOAD * INLINE [

ThreadID, TaskId, Timestamp

th0, t1, 17-10-11 7:37

th0, t1, 18-10-11 7:38

th0, t1, 19-10-11 7:42

th0, t1, 20-10-11 7:39

th0, t1, 21-10-11 7:32

th0, t1, 22-10-11 7:37

th0, t1, 23-10-11 7:31

th0, t2, 17-10-2011 7:40

th0, t2, 18-10-2011 7:52

th0, t2, 19-10-2011 7:41

th0, t2, 20-10-2011 7:39

th0, t2, 21-10-2011 7:40

];

source2:

LOAD *,

If(Peek(TaskId) = TaskId, Peek(rownumber)+1, 1) as rownumber

Resident source order by ThreadID, TaskId, Timestamp desc;

drop table source;

AvgTimeTaskId:

noconcatenate

LOAD ThreadID as ThreadID2,

TaskId as TaskId2,

Time(Avg(frac(Timestamp))) AS AverageTime

RESIDENT source2 where rownumber <= 4 group by ThreadID,TaskId;

Hope this helps,

Stefan

• ###### Average of the last X periods

BINGO . Thanks!