Updating average along time series

hi,

i am trying to add a column for my database below. it is a time series data with hourly electrical consumption over a year. i want a column which has the ratio of current consumption to that of the whole day's total consumption (sum of 24 hour) . this i can group by hour and join it with original data and do the ratio by simple division. But at the same time it is required to update (average) this ratio  every week as new data comes along. for example at 10 AM on a given week and day of week (1-sunday thru 7-saturday), i want the average of the above mentioned ratio from the weeks before my current week for every hour =>

average = { [ (week-1) + (week-2) + .... + (week1) ]/number of weeks that are before }

how can i keep updating my hourly cum weekly average of this ratio as new data comes along?

Also i have attached my database as a .table file.

Thank you,

Date time satmp                                |      day of week  |  Hour  |     elec consumption

2014-03-11T00:00:00 3 0 2.0
2014-03-11T01:00:00 3 1 2.0
2014-03-11T02:00:00 3 2 2.0
2014-03-11T03:00:00 3 3 2.0
2014-03-11T04:00:00 3 4 2.0
2014-03-11T05:00:00 3 5 2.0
2014-03-11T06:00:00 3 6 2.0
2014-03-11T07:00:00 3 7 324.8
2014-03-11T08:00:00 3 8 862.8000000000002
2014-03-11T09:00:00 3 9 755.1999999999998
2014-03-11T10:00:00 3 10 2.0
2014-03-11T11:00:00 3 11 55.8
2014-03-11T12:00:00 3 12 701.4000000000004
2014-03-11T13:00:00 3 13 2.0
2014-03-11T14:00:00 3 14 2.0
2014-03-11T15:00:00 3 15 2.0
2014-03-11T16:00:00 3 16 2.0
2014-03-11T17:00:00 3 17 2.0
2014-03-11T18:00:00 3 18 55.8
2014-03-11T19:00:00 3 19 2.0
2014-03-11T20:00:00 3 20 2.0
2014-03-11T21:00:00 3 21 647.6
2014-03-11T22:00:00 3 22 2.0
2014-03-11T23:00:00 3 23 2.0
2014-03-12T00:00:00 4 0 486.20000000000005
2014-03-12T01:00:00 4 1 1562.2
2014-03-12T02:00:00 4 2 378.59999999999985
2014-03-12T03:00:00 4 3 324.7999999999999
2014-03-12T04:00:00 4 4 271.0
2014-03-12T05:00:00 4 5 163.39999999999995
2014-03-12T06:00:00 4 6 109.60000000000001
2014-03-12T07:00:00 4 7 2.0
2014-03-12T08:00:00 4 8 2.0
2014-03-12T09:00:00 4 9 2.0

Hi Manoj,

To do this, you first need a column that identifies each week. You can get this with a Date Field Extractor node. Next, you can group by week number to get the weekly average of the currently available data, from which you then calculate the overall average. You then need to join this average back to your data.

Cheers,

Roland