2 Replies Latest reply: Aug 15, 2016 8:23 PM by ANDRE Bisson RSS

    Delay calculation between Dates

    ANDRE Bisson

      Hello Community,

      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?

       

      Other question:

      Is it also possible to load the min and max date per JobNum and Per Operation Code (or group of Operation Code).

       

      Best regards,

      Andre