Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Not standard working hours cycle time

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

5 Replies
Anonymous
Not applicable
Author

Does anyone have any idea how to resolve this?

f_rankie
Contributor II
Contributor II

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?

f_rankie
Contributor II
Contributor II

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.

isingh30
Specialist
Specialist

Can you share sample application or data?

Thanks

Anonymous
Not applicable
Author

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:

   

StartDateEndDateCycleTime
06.05.2018 22:1007.05.2018 16:05
11.05.2018 15:4414.05.2018 8:45
11.05.2018 23:4416.05.2018 23:44
12.05.2018 1:3914.05.2018 1:39
13.04.2018 21:5215.04.2018 23:29
15.04.2017 23:1921.04.2017 23:19

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