Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Discover how organizations are unlocking new revenue streams: Watch here
cancel
Showing results for 
Search instead for 
Did you mean: 
HereWeGoAgain
Contributor III
Contributor III

Excluding 0 time format from an average

Good Day

Please assist.

I'm trying to exclude zero value from arrival times. the format is the following 1900-01-00 00:00:00.

the formula that I used, but isn't working is the following: time(AVG(if(LoadingArrivalTime<>'0',LoadingArrivalTime)))

2 Solutions

Accepted Solutions
MayilVahanan

Hi @HereWeGoAgain 

Are you looking to remove zero hour info? If so,

try like below

AVG(if(Hour(LoadingArrivalTime)<>0,LoadingArrivalTime))

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.

View solution in original post

HereWeGoAgain
Contributor III
Contributor III
Author

4 Replies
MayilVahanan

Hi @HereWeGoAgain 

Are you looking to remove zero hour info? If so,

try like below

AVG(if(Hour(LoadingArrivalTime)<>0,LoadingArrivalTime))

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.
HereWeGoAgain
Contributor III
Contributor III
Author

@MayilVahanan Thank you so much!

HereWeGoAgain
Contributor III
Contributor III
Author

Hi @MayilVahanan  How will i also exclude negative numbers/time

MayilVahanan

HI @HereWeGoAgain 

Is time in negative? am not sure, for negative numbers, you can try like below

If(YourNumberfield > 0,YourNumberfield )

or

If(Sum(YourNumberfield) > 0, sum(YourNumberfield ))

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.