Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

siddharth_kulka
Contributor

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

1 Solution

Accepted Solutions
MVP
MVP

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

)

)

2 Replies
MVP
MVP

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

)

)

siddharth_kulka
Contributor

Re: How to exclude weekend hours in Qlikview?

Works perfectly, thanks swuehl

Community Browser