topic Re: Not standard working hours cycle time in QlikView

Not standard working hours cycle time

Anonymous — Mon, 14 May 2018 11:15:17 GMT

Hello Qlikview Experts,

I am struggling with counting cycle time as working hours.

I have to count working hours between two dates, but I have to take into consideration time from Sunday 23:00 (GMT) till Friday 23:00 (GMT)

For Example if StartDate is Monday 9:00 and EndDate is Friday 18:00 I would like to receive 105.00 (hours)

If StartDate is Sunday 22:00 and EndDate is Monday 5:00 I should receive 6.00 hours

If Start Date is Friday 20:00 and EndDate is Saturday 12:00 I should receive 3.00 hours

I would really appreciate any clues or comments, I have searched the community, but have not found similar case

The case is almost like networkingdays, but we have to include 1 hour from Sunday and shorten Firiday to 23:00 and this make me problem which I cannot overcome

Thank you in advance for comments

Re: Not standard working hours cycle time

Anonymous — Tue, 15 May 2018 14:17:53 GMT

Does anyone have any idea how to resolve this?

Re: Not standard working hours cycle time

f_rankie — Tue, 15 May 2018 14:31:52 GMT

For the answer it will be important to know whether you can exclude something like this:

StartDate = Thursday 15:00

EndDate = Tuesday 08:00

Can you exclude the possibility that the EndDate is 'smaller' (in terms of day of the week) than the StartDate?

Re: Not standard working hours cycle time

f_rankie — Tue, 15 May 2018 15:00:41 GMT

Assuming that you can exclude "working over the weekend" (see my previous reply), you first determine the 'legal' start and legal end of the working period:

StartPeriod = max(StartDate, <Sunday 23:00h>) - so whatever is higher will be used as start.

EndPeriod = min(EndDate, <Friday 23:00h>) - so whatever is lower will be used as end.

Period = EndPeriod - StartPeriod. - and this will give you the hours.

Of course the above is not QV code but gives you the idea. For max() (used in many programming languages) you can use NumMax() or RangeMax() in QV, and for min() you can use NumMin() or RangeMin() in QV.

Re: Not standard working hours cycle time

isingh30 — Tue, 15 May 2018 15:07:47 GMT

Can you share sample application or data?

Thanks

Re: Not standard working hours cycle time

Anonymous — Tue, 15 May 2018 19:51:40 GMT

Hello Ishtdeep,

Thanks for your comment. I have StartDate and EndDate. CycleTime should be counted as amount of hours, but only beetween Sunday 23:00 till Friday 23:00 (so 1 hours from Sunday, Mon, Tue, Wed, Thu, and 23 hours from Friday).For example if task start and end time is on Saturday CycleTime should be 0

I have to also exclude bank holidays

Below I attached example of dates which I have:

StartDate	EndDate	CycleTime
06.05.2018 22:10	07.05.2018 16:05
11.05.2018 15:44	14.05.2018 8:45
11.05.2018 23:44	16.05.2018 23:44
12.05.2018 1:39	14.05.2018 1:39
13.04.2018 21:52	15.04.2018 23:29
15.04.2017 23:19	21.04.2017 23:19

I havent found solution yet, so if somebody has any idea or clue I would really appreciate help