Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

How to get sum of next 10 minutes ofr each minute 1

Status
Not open for further replies.

vega83

Programmer
Jun 12, 2008
141
US
Friends here is the tricky part of data, I want to sum of value for next ten minutes for each minute
Here is data
Date Value
05/06/2010 9.51 am 1
05/06/2010 9.52 am 0
05/06/2010 9.53 am 1
05/06/2010 9.54 am 1
05/06/2010 9.55 am 0
05/06/2010 9.56 am 1
05/06/2010 9.57 am 0
05/06/2010 9.58 am 0
05/06/2010 9.59 am 1
05/06/2010 10 am 1
05/06/2010 10.01 am 1
05/06/2010 10.02 am 1

For
05/06/2010 9.51 am I want to show sum for next ten minutes that is from 05/06/2010 9.51 am to 05/06/2010 10.am i,e 6 and so on for each min

Date value
05/06/2010 9.51 am 6
05/06/2010 9.52 am 7
05/06/2010 9.53 am 8 so on, any help on this please?
Thanks in advance,
Vega
 
Your sample results are incorrect if you want a rolling sum--it should be 6,6,7. Or isn't that what you mean?

If you sort the datetimes in descending order, you could use the following adaption of a rolling sum formula (adapted from one by bdreed) which would show the value of the previous 9 records added to the current record:

numbervar array Accumz;
numberVar x;
numbervar z := 0;
if OnFirstRecord then
ReDim Accumz[1] else
ReDim Preserve Accumz [UBound(Accumz)+1];
Accumz [UBound(Accumz)] := {table.value};
If UBound(Accumz) < 11 then
z := Sum(Accumz) Else
(
for x := (UBound(Accumz)-9) to (UBound(Accumz)) do
(
z := z + Accumz [x];
);
z;
);
z

-LB
 
LB your right about the sum , thank you so much I will try this now.
 
LB it works like Charm , unbelievable, thanks sooo much!
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top