2 Replies Latest reply: Jun 13, 2016 7:32 PM by Siddharth Kulkarni RSS

    How to exclude weekend hours in Qlikview?

    Siddharth Kulkarni

      We have 2 date fields in play: Instructed Date and Confirmed Time

       

      The objective is to calculate "Time Between" by excluding weekend hours:

      Time Between =  Confirmed Time - Instructed Date (by excluding weekend hours if any)

       

      Question:

      How can this be achieved in Qlikview?

       

      Below is a simplified view of the requirement:

      Instructed Date

      Confirmed Time

      Time Between (To be calculated in Qlikview by excluding weekend hours)

      Friday (June 3rd  –  3PM)

      Monday (June 6th – 3PM)

      24 Hours (Excluding 48 weekend hours )

      Saturday (June 4th –  3PM)

      Monday (June 6th – 3PM)

      15 Hours (After excluding 33 weekend hours)

       

      Any help is greatly appreciated!

       

       

      Kind Regards,

      Sid

        • Re: How to exclude weekend hours in Qlikview?
          Stefan Wühl

          You should be able to do it like shown here:

          Re: Calculate hours between two Date/Time strings

           

          Interval(

          rangesum(

          NetWorkDays(DT1+1,DT2-1,$(vHol)) * MakeTime(10)  

          // 10 hours per workday, for all day inbetween the period, excluding bounderies

          ,if(NetWorkDays(DT2,DT2,$(vHol)),Rangemin(rangemax(frac(DT2),maketime(8)),maketime(18))-Rangemax(rangemin(frac(DT2),maketime(8)),maketime(8)),0) // working hours last day

          ,if(NetWorkDays(DT1,DT1,$(vHol)),Rangemin(rangemax(frac(DT1),maketime(18)),maketime(18))-Rangemax(rangemin(frac(DT1),maketime(18)),maketime(8)),0) // working hours first day

          ,if(NetWorkDays(DT1,DT1,$(vHol)) and floor(DT1)=floor(DT2),-MakeTime(10)) // correct for first equals last day

          )

          )

           

          since you are not interested in working hours, this can be simplified to

           

          Interval(

          rangesum(

          NetWorkDays(DT1+1,DT2-1,$(vHol)) * 1 // 24 hours per workday, for all day inbetween the period, excluding bounderies

          ,if(NetWorkDays(DT2,DT2,$(vHol)), frac(DT2),0) // working hours last day

          ,if(NetWorkDays(DT1,DT1,$(vHol)),1-frac(DT1),0) // working hours first day

          ,if(NetWorkDays(DT1,DT1,$(vHol)) and floor(DT1)=floor(DT2),-1 ) // correct for first equals last day

          )

          )