# QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

## 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:noconcatenateLOAD ThreadID,            TaskId,            Time(Average(frac(Timestamp))) AS Avg(Time)RESIDENT source[for the last X iterations]`
1 Solution

Accepted Solutions
MVP

## 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

4 Replies
Not applicable

## 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

Not applicable

## 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)

MVP

## 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

Not applicable

BINGO . Thanks!