Average over sets of consecutive periods of time.

 Hello

I want to creat a calculated column which calculates an average over a time frame. The time frame gets larger as the data set progresses. i.e. I want to include the previous time point in the next average.

e.g. 

Temperature
Time / Days
Time Point
Average Temperature (the column I want to calculate)
25.1 1 TP1 25.2
25.2 2 TP1 25.2
25.3 3 TP1 25.2
25.4 4 TP2 25.35
25.5 5 TP2 25.35
25.6 6 TP2 25.35
25.7 7 TP3 25.5
25.8 8 TP3 25.5
25.9 9 TP3 25.5

So the average for 'TP3' should be the average of all the data, not just data at 'TP3'. The average for TP2 should include all data from TP1 and TP2.

I've tried the following:

case  

when [Time Point]='TP1' then If([Time / Days]<=3,Avg([Storage Temperature / °C]),NULL)

when [Time Point]='TP2' then If([Time / Days]<=6,Avg([Storage Temperature / °C]),NULL)

when [Time Point]='TP3' then If([Time / Days]<=9,Avg([Storage Temperature / °C]),NULL)

end

However the "when [Time Point]='TPX'" seems to be limiting the data the following if statement sees, so the returned average is only pertaining to TPX (i.e. the average for TP3 returns as 25.8)



Does anyone have suggestions for a fix?

(1) Answer

Login