6 Replies Latest reply: May 29, 2015 1:48 AM by Hasvine Dhurmea RSS

    Networking days

    Hasvine Dhurmea

      Hello Everyone.

       

      I need to calculate the time taken for a ticket to be closed.

      I have the startdate and enddate in my table.

      I know on excel we can use the Networking days. I can use it on qlikview but the problem is that our company works from 08:00 to 18:00.

       

      networkdays(startdate, enddate)

       

      How do I include the time?

       

      thanks,

      Hasvine

        • Re: Networking days
          Sunny Talwar

          I may be over simplifying this, but maybe this is what you want:

           

          NetWorkDays(startdate, enddate) * 10

           

          NetWorkDays(startdate, enddate) will calculate the number of work days between the two dates. Multiply it by 10 hours to get the number of hours. Let me know if you are expecting a different solution.

           

          Best,

          Sunny

          • Re: Networking days
            Stefan Wühl

            If you have timestamps for start and end, date including time information, and want to know the precise duration, have a look at

            Calculate hours between two Date/Time strings

            • Re: Networking days
              Jonathan Dienst

              Assuming startdate and enddate are valid QV timestamp values:

               

              =Interval(

                   RangeMin(18/24, Frac(enddate )) - 

                   RangeMax(8/24, Frac(startdate )) +

                   RangeMax((NetworkDays(startdate , enddate ) - 1) * (18/24 - 8/24), 0)

              , 'd hh:mm')

               

              Set the format string to 'h' or 'd' to see just hours or days respectively/

                • Re: Networking days
                  Hasvine Dhurmea

                  Hi Jonathan,

                   

                  Thank you for your reply. Your post is very helpful and the result is correct.

                   

                  Is it possible in qlikview to count a day as 10 hours instead of 24 hours.

                   

                  My boss wants the result to show the 10 working hours (08:00 - 18:00) as 1 day.

                   

                  I managed to do it on excel before but am new to qlikview and I do not really know.

                   

                  Many Thanks for your precious help.

                   

                  Kind Regards,

                   

                  Hasvine

                    • Re: Networking days
                      Jonathan Dienst

                      Like this:

                       

                      Floor(

                        (RangeMin(18/24, Frac(enddate )) -

                        RangeMax(8/24, Frac(startdate )) +

                        RangeMax((NetworkDays(startdate , enddate ) - 1) * (18/24 - 8/24), 0)) / 10

                      )

                      & 'd' &

                      Interval(

                        Frac(

                        (

                        RangeMin(18/24, Frac(enddate )) -

                        RangeMax(8/24, Frac(startdate )) +

                        RangeMax((NetworkDays(startdate , enddate ) - 1) * (18/24 - 8/24), 0)

                        ) / 10

                        )

                      , 'hh:mm')