aggregating problem

I need to aggregate the temperature samples collected every day
on an hourly basis for example say I collect sample / hour of
temperature from July 1st to July 31st now I have temperature in 1 column & time (mm:dd:yyyy:hh:mm:ss:AM/PM) format now I
want to display a report say temperature Vs Hour , I have taken
my readings randomly say at all times now I need to display them
like every day in july average temp at 8:00 am , 9:00 am 10:00 am etc
but I have temp readings not exactly recorded at 8:00 one day it can be
8:01 next day 8:12 or 8:17 etc but since the hour is 8:00 it does not matter , In short a report of hour Vs temp over a month (Average values)
I see lot of date functions but no time functions in BO also to read unique
time I did substr() but this only helps visually not mathematically
can any one help


rockstart (BOB member since 2004-05-14)

I think an object like the one discussed here might be just what you need. It “rounds” a date / time field to the hour, and would be perfect to base your average aggregation on.


Dwayne Hoffpauir :us: (BOB member since 2002-09-19)

So I think you want to read 01/01/2004 08:01 as 01/01/2004 08:00. Is this right?

If so, give this a try

=SubStr(FormatDate( ,“mm/dd/yyyy HH:mm”) ,1 ,Length(FormatDate( ,“mm/dd/yyyy HH:mm”))-2)&“00”

This is a string data type.

If you’re feeling lucky, you can try to switch it back to a date data type. I would make the above a separate variable (called variable) and do the following:

ToDate(<variable,“mm/dd/yyyy hh:mm:”).

Hope this helps


SteveBickerton :canada: (BOB member since 2002-08-15)