Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
IMPORTANT security patches for GeoAnalytics Server available to download: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
HereWeGoAgain
Contributor III
Contributor III

Times in buckets

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

2 Solutions

Accepted Solutions
sunny_talwar

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

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

View solution in original post

sunny_talwar

Try this

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

View solution in original post

9 Replies
sunny_talwar

May be you need this?

Avg(If(StartTime > MakeTime(4) and StartTime < MakeTime(6), StartTime))
HereWeGoAgain
Contributor III
Contributor III
Author

@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
Contributor III
Contributor III
Author

@sunny_talwar Hi

 

Yes, not working

Below is my "startTime" field calculation

HereWeGoAgain_0-1623658306904.png

 

sunny_talwar

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
Contributor III
Contributor III
Author

@sunny_talwar Excellent!, Thank you so much!

HereWeGoAgain
Contributor III
Contributor III
Author

@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)))

HereWeGoAgain_0-1623917563940.png

 

sunny_talwar

Try this

Time(Avg(If(Frac(StartTime) > MakeTime(4) and frac(StartTime) < MakeTime(6), Frac(StartTime))))
HereWeGoAgain
Contributor III
Contributor III
Author

@sunny_talwar Thanks!