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

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
rubenmarin

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

View solution in original post

6 Replies
rubenmarin

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

HereWeGoAgain
Contributor III
Contributor III
Author

Hi @rubenmarin , thank you for your reply.

 

i'm getting an error with the average

 

HereWeGoAgain_0-1637563924652.png

 

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

rubenmarin

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

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

HereWeGoAgain_0-1637746152051.png

 

Thanks in Advance

 

rubenmarin

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.

HereWeGoAgain
Contributor III
Contributor III
Author

Thank you @rubenmarin