# 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:
Contributor III

## Time does not display in KPI based on Client

Good Day

please assist with checking my formula, i'm having trouble displaying time in a KPI chart based on a specific client

Current Formula: time(AVG(if(Hour(LoadTurnAroundTime)<>0 and IF(MATCH([DrNumber-Debtor]='UNI016','UNI017'),[DrNumber-Debtor]),LoadTurnAroundTime)))

Thanks in Advance
Kind Regards

1 Solution

Accepted Solutions
MVP

Hi, if you want the Avg of LoadTurnAroundTime it could be:

time(AVG(if(Hour(LoadTurnAroundTime)<>0 and MATCH([DrNumber-Debtor],'UNI016','UNI017'),LoadTurnAroundTime)))

Or converted to set analysis (wich gives better performance):

Time(Avg({<LoadTurnAroundTime={">0"},[DrNumber-Debtor]={'UNI016','UNI017'}>} LoadTurnAroundTime))

This will work if LoadTurnAroundTime it's the time used (not a timestamp with start/end datetime) and if you want to count every minute, (with "Hour(LoadTurnAroundTime)<>0" times lower than a full hour are ignored).

6 Replies
MVP

Hi, you don't nee two Ifs, just onme with an and, also Macth is separted with commas, not with equal sign:

time(AVG(if(Hour(LoadTurnAroundTime)<>0 and MATCH([DrNumber-Debtor],'UNI016','UNI017'),[DrNumber-Debtor]),LoadTurnAroundTime))

Contributor III
Author

Hi @rubenmarin , thank you for your reply.

i'm getting an error with the average

i'm open to any other formula where i can get an avg time(time must exclude any zero values or negative values) on multiple accounts.

Thanks in Advance

MVP

Hi, if you want the Avg of LoadTurnAroundTime it could be:

time(AVG(if(Hour(LoadTurnAroundTime)<>0 and MATCH([DrNumber-Debtor],'UNI016','UNI017'),LoadTurnAroundTime)))

Or converted to set analysis (wich gives better performance):

Time(Avg({<LoadTurnAroundTime={">0"},[DrNumber-Debtor]={'UNI016','UNI017'}>} LoadTurnAroundTime))

This will work if LoadTurnAroundTime it's the time used (not a timestamp with start/end datetime) and if you want to count every minute, (with "Hour(LoadTurnAroundTime)<>0" times lower than a full hour are ignored).

Contributor III
Author

Thanks  Ruben,

This formula works perfect: time(AVG(if(Hour(LoadTurnAroundTime)<>0 and MATCH([DrNumber-Debtor],'UNI016','UNI017'),LoadTurnAroundTime)))

is there anyway i can include something into the formula to exclude negative values?

i have a few due to typo's :

Thanks in Advance

MVP

Hi, maybe just?: time(AVG(if(Hour(LoadTurnAroundTime)>0 and MATCH([DrNumber-Debtor],'UNI016','UNI017'),LoadTurnAroundTime)))

If you to ignore them only after the AVG is calcualted it may be like: Time(If(avg(...)>0,Avg(..)), but if it's a type maybe you want the first to avoid that typos affcet the avg.

Contributor III
Author

Thank you @rubenmarin