Qlik Community

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:

noconcatenate

LOAD ThreadID,

           TaskId,

           Time(Average(frac(Timestamp))) AS Avg(Time)

RESIDENT source

[for the last X iterations]

1 Solution

Accepted Solutions
MVP
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
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

Average of the last X periods

BINGO . Thanks!

Community Browser