Time does not display in KPI based on Client

[HereWeGoAgain]

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

[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]

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

[rubenmarin · Accepted solution]

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]

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

[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]

Thank you @rubenmarin