Hi folks,

I work with sets of data in which there is data provided for each
hour over periods of several years.

I often have to calculate "rolling averages" in which the result
displays the average of values over the prior 30 days (the prior 720
hourly records). [That part is easy enough -- normally it can be done
by a simple relationship based on a calculation that establishes a
range of dates, from the current date and hour, backwards to 720
hours earlier; the current date and hour is that of the current
record (the upper end of the date-hour range), and the calculation
sets the lower end of the date-hour range.]

Here's the hitch:

I need to find a way to extend the range under certain conditions.

Specifically, whenever there are no entries during a whole calendar
day ((all 24 hourly entries for that particular date are blank,
e.g.), then I need to skip that date and not count it as one of the
days in the "30-day" average, and extend the range of records
averaged by an extra day (an extra 24 hourly records).

This can happen several times in a month. For example, if there are
three days with no data entries at all, then I really need to average
over the previous 33 days (792 hours / 792 records), to make up for
the 3 days with no data. If there are 7 days with no data, then I
really need to average existing entries over 37 days, and so forth.

It is easy to pick out which days have non-zero entries, based on a
simple self-join based on the date of each entry, that sums all
hourly data entries for that date.

Is there a good way to set up a calculation to set the right range,
to encompass a period including 30 days of non-zero data?

Desperately needed. Any help much appreciated.

Thanks.
--

#############################################################
This message is sent to you because you are subscribed to
the mailing list <fmpro@lists.lassosoft.com>.
To unsubscribe, E-mail to: <fmpro-off@lists.lassosoft.com>
To switch to the DIGEST mode, E-mail to <fmpro-digest@lists.lassosoft.com>
To switch to the INDEX mode, E-mail to <fmpro-index@lists.lassosoft.com>
Send administrative queries to <fmpro-request@lists.lassosoft.com>