This content has been marked as final. Show 2 replies
I'm pretty new using Qlik Sense, so I need help of expert.
I'm trying to calculate the average delay between First In and Last Out per JobNum and per Operation Code.
Note: Operation Code may regroup 2 different one, by example OperA and OperB = OperC (here i'm using Wildmatch to group them together), so in large I will like to find the Min date of OperA or OperB per JobNum and the Max date of OperA or OperB per JobNum and substract them together)
I've done the following:
Create a Pivot Table with Month Grouping (in axis X) and Operation Code (in ordonate Y) and using the formula: avg(Distinct(Aggr((max(DateTime) - Min(DateTime)), (JobNum),[Operation Code])))
This give me two different result:
1- When looking at overall I'm getting a delay (Let say = 14:53:08)
2- when selecting a specific month I'm getting a different delay (let say = 6:04:04 which is the right answer)
I'm guessing it's because in the Data, some JobNum may fall in 2 different month, but not sure. If so how to fix the issue?
Is it also possible to load the min and max date per JobNum and Per Operation Code (or group of Operation Code).