topic Re: Exclude Specific Hours in weekends - Time Calculation in QlikView

Exclude Specific Hours in weekends - Time Calculation

Anonymous — Mon, 04 Aug 2014 05:47:55 GMT

Dear Experts,

Kindly refer the attachment.

I want to calculate the time taken for the process flow.ie.Time taken from "In Queue" to "Complete".But here I want to exclude the hours from Friday 7PM to Monday 8AM.

I tried the networkdays,but it is not giving the expected answer since I need specific hours only.

Could you please help me

Thanks & Regards

Jeba

Re: Exclude Specific Hours in weekends - Time Calculation

Anonymous — Mon, 04 Aug 2014 07:11:47 GMT

Experts,could you please help me in this logic

Re: Exclude Specific Hours in weekends - Time Calculation

Anonymous — Mon, 04 Aug 2014 11:21:57 GMT

Experts,could you please help me in this logic

Re: Exclude Specific Hours in weekends - Time Calculation

tresB — Mon, 04 Aug 2014 11:31:24 GMT

Could you explain your requirement a bit more with expected output against a sample data?

Re: Re: Exclude Specific Hours in weekends - Time Calculation

Anonymous — Mon, 04 Aug 2014 11:42:36 GMT

Hi Tresesco,

Thanks a lot for your reply.

Attaching the qlikview application I have got previously which was working perfectly except this scenario.

Also attaching the updated excel file with some more details.

Kindly refer the highlighted rows.

In this scenario,Team 3 has started their work on 13.06.14 at 17.52 Hours and they completed that work on 16.06.14 at 12 Hours.If we directly calculate the hours between it will come at 66.08 Hours which is not correct.I want to exclude the hours from Friday 7 PM to Monday 8AM.so actual time taken was 6.08 Hours.The networkdays function is not giving the correct solution,because I have specific times.

May I request you to help me.

Thanks & Regards

Jeba

Re: Exclude Specific Hours in weekends - Time Calculation

tresB — Mon, 04 Aug 2014 11:51:57 GMT

I would follow this: Re: Calculate hours between two Date/Time strings

Re: Exclude Specific Hours in weekends - Time Calculation

Anonymous — Tue, 05 Aug 2014 06:41:27 GMT

Hi Tresesco,

As per this link they have used the static inline table,but my data is not static and the code has to work for all the weekends moving forward.

Could you please help me

Thanks & Regards

Jeba

Re: Exclude Specific Hours in weekends - Time Calculation

tresB — Tue, 05 Aug 2014 06:53:51 GMT

Dear Jeba,

The inline data in the post is being used as input data for instance, it has not necessarily to be so, you can take your loaded data. Please go through the post patiently, I am sure, it would be able to direct you to your goal.

Re: Exclude Specific Hours in weekends - Time Calculation

Tue, 05 Aug 2014 07:36:06 GMT

Hi,

Can the difference between Complete Date and InQueue Date be more than one week ??

if not then you can use formula like this

=if(WeekDay(Timestamp#(InQueue Date,'MM/DD/YYYY hh:mm'))>WeekDay(Timestamp#(Complete Date,'MM/DD/YYYY hh:mm')),interval(Interval(Timestamp#(Complete Date,'MM/DD/YYYY hh:mm')-Timestamp#(InQueue Date,'MM/DD/YYYY hh:mm'),'hh:mm')-timestamp('60:00','hh:mm'),'hh:mm'),Interval(Timestamp#(Complete Date,'MM/DD/YYYY hh:mm')-Timestamp#(InQueue Date,'MM/DD/YYYY hh:mm'),'hh:mm'))

Re: Exclude Specific Hours in weekends - Time Calculation

Anonymous — Tue, 05 Aug 2014 07:44:04 GMT

Hi Vivek,

Its not predicatable.

I have seen many cases which is more than one week/month

Thanks & Regards

Jeba

Re: Exclude Specific Hours in weekends - Time Calculation

Anonymous — Tue, 05 Aug 2014 07:47:41 GMT

Hi Tresesco,

Yes,I almost got the logic and thanks a lot for your patience and guidance.

This is working based on specific hours during all working days,however the requestor asked me to ignore only the specific weekend hours not all working days.

Now I have suggested the requestor to calculate only the office hours.I am waiting for their confirmation.

Mean while if you find anything interesting to exclude only the specific weekend hours kindly message me.

Again thanks a lot for your timely help.

Thanks & Regards

Jeba

Re: Exclude Specific Hours in weekends - Time Calculation

Tue, 05 Aug 2014 07:49:44 GMT

Hi,

Then its very difficult to find the logic,

I tried it and I can't figure out the logic,

wait for the Experts reply .