Hi ,
I used the following expression to calculate average working hours and it's fine:
avg(fabs(interval(((Networkdays(Date(TerminationFirstTimestamp,'DD/MM/YYYY hh:mm:ss'),Date(ReferralLastOnly,'DD/MM/YYYY hh:mm:ss')) -2)*'$(vWorkDay)')
+
if(frac(date(TerminationFirstTimestamp))<num('$(vQuitTime)'),if(frac(date(TerminationFirstTimestamp))>num('$(vStartTime)'),Date#(date(floor(TerminationFirstTimestamp),'DD/MM/YYYY') & '$(vQuitTime)' ,'DD/MM/YYYYhh:mm:ss')-Date(TerminationFirstTimestamp,'DD/MM/YYYY hh:mm:ss'),Date#(date(floor(TerminationFirstTimestamp),'DD/MM/YYYY') & '$(vQuitTime)' ,'DD/MM/YYYYhh:mm:ss')-Date#(date(floor(TerminationFirstTimestamp),'DD/MM/YYYY') & '$(vStartTime)' ,'DD/MM/YYYYhh:mm:ss')),0)
+
if(frac(date(ReferralLastOnly))>num('$(vStartTime)'),if(frac(date(ReferralLastOnly))<num('$(vQuitTime)'),(Date(ReferralLastOnly,'DD/MM/YYYY hh:mm:ss')-Date#(date(floor(ReferralLastOnly),'DD/MM/YYYY') & '$(vStartTime)','DD/MM/YYYYhh:mm:ss')),Date#(date(floor(ReferralLastOnly),'DD/MM/YYYY') & '$(vQuitTime)','DD/MM/YYYYhh:mm:ss')-Date#(date(floor(ReferralLastOnly),'DD/MM/YYYY') & '$(vStartTime)','DD/MM/YYYYhh:mm:ss')),0), 'hh:mm:ss')))*24
There are outliers in the above expression and want to get rid of these e.g. as below. Could you please tell how can I do.
| Channel Rework |
| 22.9 |
| 50.2 |
| 65.2 |
| N/A |
| N/A |
| N/A |
| 4.8 |
| 2.1 |
| N/A |
| 0.7 |
| N/A |
| N/A |
| 10.1 |
| N/A |
| N/A |
| 344.2 |
