How can we get the sum of n rows from the actual table in Load?

[#1]

Hi All,

I wan to calculate the sum of past 2 rows + current row  + sum of next 2 rows while loading itself.

For eg,

Actual Table:

Date	Production
01/10/2013	20
02/10/2013	15
03/10/2013	10
04/10/2013	23
05/10/2013	65
06/10/2013	0
07/10/2013	2

Eg: Load Date, Production, ( Calculation = Past 2 values + current value + next 2 values) as Expected Result;

      SQL Select * from Actual Table;

Expected Load data:

Date	Production	Expected Result = Past 2 values + current value + next 2 values
01/10/2013	20	20 + 15 + 10 =  45
02/10/2013	15	20 + 15 + 10 + 23 = 68
03/10/2013	10	20 + 15 + 10 + 23 + 65 = 133
04/10/2013	23	15 + 10 + 23 + 65 + 0 = 113
05/10/2013	65	10 + 23 + 65 + 0 + 2 = 100
06/10/2013	0	23 + 65 + 0 + 2 = 90
07/10/2013	2	65 + 0 + 2 = 67

Where  Green - Past values, 

           Purple - Current Value

           Blue - Next Values  and

           Red - Final Result

I hope this is enough to understand and give answer for my question.

Please give your answers. I am awaiting your response.

[swuehl]

Maybe like this:

Set DateFormat = 'DD/MM/YYYY';

INPUT:

LOAD Date,

     Production

FROM

[http://community.qlik.com/thread/98253]

(html, codepage is 1252, embedded labels, table is @1);

TMP:

LOAD *,

          rangesum(peek(Production),Peek(Production,-2),Production) as TMPSum

Resident INPUT ORDER BY Date desc;

RESULT:

LOAD Date,

          Production,

          rangesum(Peek(Production,-2),Peek(Production),TMPSum) as Sum

Resident TMP ORDER BY Date asc;

drop table INPUT;

drop table TMP;

[tresesco]

May be like attached sample?

[Gysbert_Wassenaar]

Or if you want a chart expression try a straight table or pivot table with Date and Production as dimensions and as expression: rangesum(Above(total sum(Production),0,3),Below(total sum(Production),1,2))

[#5]

I did something as same as you said. But while zooming I am not getting the actual values. It varies depending upon the selection. So I want to do it while loading values. If we can fix this zooming with your solution, Please let me know...

[Gysbert_Wassenaar]

If you don't want selections to influence the values then you can't use a chart expression, but you need to calculate the values in the script like Stefan showed above.

[tresesco · Accepted solution]

Did you check my earlier attachment? I thought it was working.

[#8]

Yes, its really useful for me to fix my issue. Thanks