averge for every one hour

[SunilChauhan]

hello all,

i have a excel sheet which have data like below

scenter time avg

sc00001 07:15:00 10

sc00001 07:30:00 20

sc00001 07:45:00 30

sc00001 08:00:00 40

sc00001 08:15:00 10

sc00001 08:30:00 20

sc00001 08:45:00 30

sc00001 09:00:00 40

now I want avg of amount for every hour.i.e according to above recard i have 2 resultiing recard like this

sc00001 07-08 100

sc00001 08-09 100

its urget .

[Anonymous]

Hi Sunil,

There are several ways to solve this I think.
My suggestion is to load the hours seperately in your loading script.
You can do this by adding the line below to your script:

subfield(time, ':' ,1) as hour,

In the attached file is an example of your table.

[#3]

Hi Sunil,

I think you would want to load the script using "hour(time) as Hour" as a new field.

Then on front-end use the AGGR function, such as:

avg(aggr(sum(Avg), Hour))

[#4]

Sorry, Dennis is right. Looked at your end results again.

You want sum(Avg), not average of the values. Disregard the AGGR function I mention.

[SunilChauhan]

hello chinu jankiram,

i believ u r going right.

i want avg of field avg which are starting from 07:15:00 to 08:00:00 i.e result is like below

sc00001 25 i.e between 07:15:00 to 08:00:00

waiting for your reply

[#6]

Hi Sunil

I made it more complicated than it needs be.

In your load statement, create an hour field such as "hour(time) as Hour", this will group all time values in the hour that they belong to.

If you want a time interval display, you can even set a preceding load statement as

LOAD *,

Hour & '-' & Hour+1 as HourIntervalDisplay;

LOAD

scenter,

time,

avg

hour(time) as Hour

From "yoursource";

On the front end, just put in a straight table with scenter, HourIntervalDisplay and an expression of avg(avg), this will give the hourly average.

Hope it helps.

[Anonymous]

See attachment.