Times in buckets

[HereWeGoAgain]

Hi Guys

could you please assist where i am going wrong with my formula:

i want to calculate the the average time between 04:00 and 06:00 (bucket)

aggr(avg(if(StartTime>'04:00' and StartTime<'06:00')),StartTime)

Thanks in advance
Kind Regards

[sunny_talwar]

May be you need this?

Avg(If(StartTime > MakeTime(4) and StartTime < MakeTime(6), StartTime))

[HereWeGoAgain]

@sunny_talwar  Hi

Avg(If(StartTime > 4 and StartTime < 6, StartTime))

not working, my Startime field info consists of HH:MM:SS

hope this helps

[sunny_talwar]

Did you try this?

Avg(If(StartTime > MakeTime(4) and StartTime < MakeTime(6), StartTime))

[HereWeGoAgain]

@sunny_talwar Hi

Yes, not working

Below is my "startTime" field calculation

[sunny_talwar · Accepted solution]

Oh may be you have date in your time field... try this

Avg(If(Frac(StartTime) > MakeTime(4) and frac(StartTime) < MakeTime(6), StartTime))

[HereWeGoAgain]

@sunny_talwar Excellent!, Thank you so much!

[HereWeGoAgain]

@sunny_talwar Hi,

I have used to above formula, but the avg. time doesn't seem to take the time between the two band 04:00-06:00

Formula used: Time(Avg(If(Frac(StartTime) > MakeTime(4) and frac(StartTime) < MakeTime(6), StartTime)))

[sunny_talwar · Accepted solution]

Try this

Time(Avg(If(Frac(StartTime) > MakeTime(4) and frac(StartTime) < MakeTime(6), Frac(StartTime))))

[HereWeGoAgain]

@sunny_talwar Thanks!