7 Replies Latest reply: Nov 7, 2013 9:41 AM by Rajaram R

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

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:

DateProduction
01/10/201320
02/10/201315
03/10/201310
04/10/201323
05/10/201365
06/10/20130
07/10/20132

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

SQL Select * from Actual Table;

DateProductionExpected Result = Past 2 values + current value + next 2 values
01/10/20132020 + 15 + 1045
02/10/20131520 + 15 + 10 + 23 = 68
03/10/20131020 + 15 + 10 + 23 + 65 = 133
04/10/20132315 + 10 + 23 + 65 + 0 = 113
05/10/20136510 + 23 + 65 + 0 + 2 = 100
06/10/2013023 + 65 + 0 + 2 = 90
07/10/2013265 + 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.

• ###### Re: How can we get the sum of n rows from the actual table in Load?

Maybe like this:

Set DateFormat = 'DD/MM/YYYY';

INPUT:

Production

FROM

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

TMP:

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

Resident INPUT ORDER BY Date desc;

RESULT:

Production,

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

Resident TMP ORDER BY Date asc;

drop table INPUT;

drop table TMP;

• ###### Re: How can we get the sum of n rows from the actual table in Load?

May be like attached sample?

• ###### Re: How can we get the sum of n rows from the actual table in Load?

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

• ###### Re: How can we get the sum of n rows from the actual table in Load?

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

• ###### Re: How can we get the sum of n rows from the actual table in Load?

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.

• ###### Re: How can we get the sum of n rows from the actual table in Load?

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

• ###### Re: How can we get the sum of n rows from the actual table in Load?

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