6 Replies Latest reply: Jun 24, 2014 9:05 AM by Marc LOUESSE RSS

    Interval calculation

    Marc LOUESSE

      Hi,

      I'm a fresh new user of Qlikview.

      I have an excel database organized as per below :

       

      Entity - Date of Arrival - Start Working time per gang - End Working time per gang - Date of Departure

       

      Which leads to this king of data :

      A - 24/06/2014 08:00 - 24/06/2014 08:12 - 24/06/2014 17:00 - 24/06/2014 20:00

      A - 24/06/2014 08:00 - 24/06/2014 08:03 - 24/06/2014 12:17 - 24/06/2014 20:00

      A - 24/06/2014 08:00 - 24/06/2014 13:17 - 24/06/2014 18:15 - 24/06/2014 20:00

      B - 20/06/2014 23:00 - 21/06/2014 01:12 - 21/06/2014 05:12 - 21/06/2014 08:00

      B - 20/06/2014 23:00 - 20/06/2014 23:15 - 21/06/2014 07:55 - 21/06/2014 08:00

       

      So fear each entity, Date of Arrival and Date of Departure are the same. What is changing for each entity is the Start and End of Working time per gang. I would like to calculate the waiting time for each entity before and after work times.

       

      Therefore for entity A, I need to calculate :

      Waiting time before start of work = "earliest Start Working time for entity A" - 'Date of Arrival" = "24/06/2014 08:03" - "24/06/2014 08:00" = 3 minutes

      Waiting time after end of work = "Date of Departure" - "latest End Working time for entity A" = "24/06/2014 20:00" - "24/06/2014 18:15" = 1 hour and 45 minutes

       

      How can I do that with Qlikview ?

       

      Thanks

        • Re: Interval calculation
          Gysbert Wassenaar

          Use entity as dimension and two expressions:

           

          interval(min([Start Working time per gang]) - [Date of Arrival], 'hh:mm:ss')

          interval([Date of Departure] - max([Start Working time per gang]), 'hh:mm:ss')

           

          If your datetimes are string values then first make numeric timestamps from them in the script:

           

          LOAD

               Entity,

               timestamp#([Start Working time per gang],'DD/MM/YYYY hh:mm') as [Start Working time per gang],

          ...etc

          FROM ...mysource...;

            • Re: Interval calculation
              Marc LOUESSE

              Thank you for your quick answer, it's working.

              I missed to mention that I have several occurrences for each Entity and now I want to average them :

               

              Entity - Occurence - Date of Arrival - Start Working time per gang - End Working time per gang - Date of Departure


              A - Occurence 1 - 24/06/2014 08:00 - 24/06/2014 08:12 - 24/06/2014 17:00 - 24/06/2014 20:00

              A - Occurence 1 - 24/06/2014 08:00 - 24/06/2014 08:03 - 24/06/2014 12:17 - 24/06/2014 20:00

              A - Occurence 1 - 24/06/2014 08:00 - 24/06/2014 13:17 - 24/06/2014 18:15 - 24/06/2014 20:00

              A - Occurence 2 - 15/06/2014 12:00 - 15/06/2014 12:05 - 15/06/2014 15:05 - 15/06/2014 18:00

              A - Occurence 2 - 15/06/2014 12:00 - 15/06/2014 13:00 - 15/06/2014 14:00 - 15/06/2014 18:00

              B - etc

               

              So Waiting time before work for Entity A - Occurence 1 = 3 minutes

              Waiting time before work for Entity A - Occurence 2 = 5 minutes

              I want to show Average time before work for Entity A = 4 minutes

               

              I set Entity as dimensions and used :

              avg(interval(min([Start Working time per gang]) - [Date of Arrival], 'hh:mm:ss'))


              But I get no result.