Calculate only Saturday working hours calculation

rakeshkumar1890 — Wed, 04 Jul 2018 04:16:15 GMT

Hi folks,

here I want to calculate working hours only for Saturday, I was tried too many thread but not found for Saturday working hours.

Suppose I have

Start_Date : 06/16/2018 10:40:30 AM, 06/23/2018 09:15:30 AM, 06/30/2018 07:30:35 AM

End_Date : 06/16/2018 12:00:05 PM, 06/23/2018 17:10:05 PM, 07/01/2018 00:00:35 AM

Working Hours time

Start_Time : 08 am

End_Time : 17 pm

Lunch_Start : 13 pm

Lunch_End : 13:30 pm

Thanks for advanced and immediate response

Regards

Rakesh

Re: Calculate only Saturday working hours calculation

Anonymous — Wed, 04 Jul 2018 07:35:40 GMT

Hope you can try like this.

Ex:-

Interval match(Actual Date) Load StartDate, EndDate

Resident AssemblyLine;

Re: Calculate only Saturday working hours calculation

rakeshkumar1890 — Wed, 04 Jul 2018 08:26:14 GMT

Now I using this script which is well worked for Monday to Friday,

but the requirement is for Saturday calculation between Start Hours 8:30 am to End 17:30 pm

LET vSatStartHour = 8;

LET vSatEndHour = 17;

LET vSatWorkingHourPerDay = ($(vSatEndHour) -$(vSatStartHour))-($(vLunchEnd)-$(vLunchStart));

LET vStartHour = 12;

LET vEndHour = 21;

LET vLunchStart =0;

LET vLunchEnd =0;

LET vWorkingHourPerDay = ($(vEndHour) -$(vStartHour))-($(vLunchEnd)-$(vLunchStart));

Holidays:

LOAD Concat(chr(39)&Holidays&chr(39),',') as Holidays Inline [

Holidays

08/04/2018

25/04/2018

];

LET vHolidays = Peek('Holidays',0,'Holidays');

SLA_Analysis:

LOAD *,

Interval(rangesum(round( rangesum(

NetWorkDays(START_TIME+1,END_TIME-1,$(vHolidays)) * MakeTime($(vWorkingHourPerDay)), // 12 hours per workday, for all day inbetween the period, excluding bounderies

if(NetWorkDays(END_TIME,END_TIME,$(vHolidays)) ,

rangesum(rangemin(frac(END_TIME),MakeTime($(vLunchStart)))- rangemin(frac(END_TIME),MakeTime($(vStartHour))) ,

rangemin(frac(END_TIME),MakeTime($(vEndHour))) - rangemin(frac(END_TIME),MakeTime($(vLunchEnd)))),0), // working hours last day

if(NetWorkDays(START_TIME,START_TIME,$(vHolidays)),

rangesum(MakeTime($(vLunchStart)) - rangemin(rangemax(frac(START_TIME), MakeTime($(vStartHour))),MakeTime($(vLunchStart))),

MakeTime($(vEndHour)) - rangemax(rangemin(frac(START_TIME), MakeTime($(vEndHour))),MakeTime($(vLunchEnd)))),0), // working first day

if(NetWorkDays(START_TIME,START_TIME,$(vHolidays)) and floor(START_TIME)=floor(END_TIME),-MakeTime($(vWorkingHourPerDay))) // If the same day then correct the hours

)*24,0.01))/24,'mm') AS Exclude_Saturday,

LOAD *,

timestamp(timestamp#(START_DATE,'MM/DD/YYYY hh:mm:ss TT')) as START_TIME,

timestamp(timestamp#(END_DATE,'MM/DD/YYYY hh:mm:ss TT')) as END_TIME;

topic Calculate only Saturday working hours calculation in QlikView

Calculate only Saturday working hours calculation

Re: Calculate only Saturday working hours calculation

Re: Calculate only Saturday working hours calculation