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

# How to exclude weekend hours in Qlikview?

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?

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

)

)

• ###### Re: How to exclude weekend hours in Qlikview?

Works perfectly, thanks swuehl