topic Re: Exclude Weekends (Friday and Saturday) and non working hours from Dates Difference in QlikView

Exclude Weekends (Friday and Saturday) and non working hours from Dates Difference

mdmukramali — Sat, 22 Aug 2015 16:37:53 GMT

Dear All,

In my sample file i have two dates

1) TRANSFER_DATE

2)NEXT_TRANSFER_DATE

I am trying to calculate the difference between two dates in hours by excluding Weekends ( which are Friday and Saturday ) and non working hours on working days.

Working Hours : 8 AM to 4 PM..

i try tried using the networkdays but it's excluding Saturday and Sunday.

so please anyone help me to sort out the issue.

kindly find the attached sample file.

Thanks,

Mukram.

Re: Exclude Weekends (Friday and Saturday) and non working hours from Dates Difference

Anonymous — Sat, 22 Aug 2015 16:59:02 GMT

hi mohammed,

you can change the order of DayNames variable

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='$#,##0.00;($#,##0.00)';

SET TimeFormat='h:mm:ss TT';

SET DateFormat='M/D/YYYY';

SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET DayNames='Sun;Mon;Tue;Wed;Thu;Fri;Sat';

instead of SETDayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

and save application

Dual(Subfield('$(DayNames)',';',WeekDay(Date)+1),Mod(Date-1,7)) as WeekDay

and use Networkdays function

Re: Exclude Weekends (Friday and Saturday) and non working hours from Dates Difference

maxgro — Sat, 22 Aug 2015 17:31:16 GMT

I try to adapt a .qvw I posted here

Re: Calculate Elapsed Time Nett‌

see attachment

in the same post you can find some useful resoruces (Gysbert's answer)

Re: Exclude Weekends (Friday and Saturday) and non working hours from Dates Difference

mdmukramali — Sun, 23 Aug 2015 13:40:49 GMT

Dear Massuno,

Thanks for the excellent Solution.

but i have 500,000 rows of data .

which is taking too long time to reload the data. ( More than 3 hours)

any other way is possible to get the solution.

Thanks,

Mukram.

Re: Exclude Weekends (Friday and Saturday) and non working hours from Dates Difference

Anonymous — Sun, 23 Aug 2015 16:51:11 GMT

See if this thread helps you.Re: Urgent: calculating working hours from 2 date fields

You have of course to adjust to your weekend and working hours.

The latest version on the very bottom.

Re: Exclude Weekends (Friday and Saturday) and non working hours from Dates Difference

mdmukramali — Sun, 23 Aug 2015 20:25:19 GMT

Hi Michael,

I tried your solution but it's not excluding the Friday and Saturday as weekends.

Thanks,
Mukram.

Re: Exclude Weekends (Friday and Saturday) and non working hours from Dates Difference

Anonymous — Tue, 25 Aug 2015 11:25:31 GMT

Hi Mukram,

I never said it did, that's why it needs adjustments for your definition of weekends and start/end time. As for the start/end - they are variables at the beginning of my script. Weekend definition, I didn't try other than Saturday/Sunday, hope you'll figure out how to change it,

Regards,

Michael