How to exclude weekend hours in Qlikview?

[siddharth_kulka]

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

[swuehl · Accepted solution]

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

)

)

[siddharth_kulka]

Works perfectly, thanks swuehl