topic How to exclude weekend hours in Qlikview? in QlikView

How to exclude weekend hours in Qlikview?

siddharth_kulka — Mon, 13 Jun 2016 21:13:52 GMT

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 6^th – 3PM)	24 Hours (Excluding 48 weekend hours )
Saturday (June 4^th – 3PM)	Monday (June 6^th – 3PM)	15 Hours (After excluding 33 weekend hours)

Any help is greatly appreciated!

Kind Regards,

Sid

Re: How to exclude weekend hours in Qlikview?

swuehl — Mon, 13 Jun 2016 21:44:29 GMT

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?

siddharth_kulka — Mon, 13 Jun 2016 23:32:59 GMT

Works perfectly, thanks swuehl