Each application has different stages i.e. (Outbound ::::::::> Customer Time :::::::> Inbound ::::::::> Security::::::> Booked). Application can come in sequence in all trays once or more than once. Also application can skip any tray depending on product type. e.g. overdraft then it will not go in customer time tray and will move directly into inbound tray. Each tray is timestamp. I want to calculate difference between two trays timestamps e.g. (Customer Time - Outbound) in working hours. I got the following expression from qlikview community and it works well if I take application by application. But the average is incorrect because application can skip any tray or only in one tray and has to move in the next tray. So the next tray counts as 0 and then zero lowers the average.
In the following expression saying if WorkflowElapseDays='Inbound' then take the difference between CMP_Date (completion timestamp of Inbound tray) and Pre Date (previous tray timestamp, could be customer time or outbound tray). How can I exclude zeros in calculating average for the following expression or any other better idea for calculations
sum ({<WorkflowElapseDays={'Inbound'}>}fabs(interval(((Networkdays(Date(pre,'DD/MM/YYYY hh:mm:ss'),Date(CMP_Date,'DD/MM/YYYY hh:mm:ss')) -2)*'$(vWorkDay)')
+
if(frac(date(pre))<num('$(vQuitTime)'),if(frac(date(pre))>num('$(vStartTime)'),Date#(date(floor(pre),'DD/MM/YYYY') & '$(vQuitTime)' ,'DD/MM/YYYYhh:mm:ss')-Date(pre,'DD/MM/YYYY hh:mm:ss'),Date#(date(floor(pre),'DD/MM/YYYY') & '$(vQuitTime)' ,'DD/MM/YYYYhh:mm:ss')-Date#(date(floor(pre),'DD/MM/YYYY') & '$(vStartTime)' ,'DD/MM/YYYYhh:mm:ss')),0)
+
if(frac(date(CMP_Date))>num('$(vStartTime)'),if(frac(date(CMP_Date))<num('$(vQuitTime)'),(Date(CMP_Date,'DD/MM/YYYY hh:mm:ss')-Date#(date(floor(CMP_Date),'DD/MM/YYYY') & '$(vStartTime)','DD/MM/YYYYhh:mm:ss')),Date#(date(floor(CMP_Date),'DD/MM/YYYY') & '$(vQuitTime)','DD/MM/YYYYhh:mm:ss')-Date#(date(floor(CMP_Date),'DD/MM/YYYY') & '$(vStartTime)','DD/MM/YYYYhh:mm:ss')),0), 'hh:mm:ss'))*24)/count(distinct Application_Id)
