Qlik Community

Qlik Education Discussions

Discussion Board for collaboration related to Qlik Education.

Not applicable

How to add 3 working hours in a expression

Hi All,

we have a nr of working hours to complete a task,

Our working hours is from Monday to Friday 8am to 5pm, If I get a task at 4pm in the afternoon and the turnaround time for this specific task is within 3 working hours then I would like to add an expression to show that the new tasks must be completed by 10am the following morning.

This will then help us to prioritise the task that is closed to their specific deadline,

I know you can use the below in to an expression and it works but it does not take working hours in to consideration

+time#(3,'hh')

Any ideas how to add this expression

Kind regards,

Sharette

1 Solution

Accepted Solutions
Employee
Employee

Re: How to add 3 working hours in a expression

can you please try this and let me know if it works?

=if(

     hour(time([SQRRfCD])+3)<17,

          timestamp(num([SQRRfCD]+0.125)),

          timestamp(num([SQRRfCD]+0.75))

     )

Regards

Agis

11 Replies

Re: How to add 3 working hours in a expression

Provide little sample data...plz

Not applicable

Re: How to add 3 working hours in a expression

Deadline.JPG.jpg

Dear Manish,

Basic data that I work on is as follow

Received new task @ 2014/08/14 16:00:00 PM this task needs to be competed by 2014/08/15 10:00:00 AM as our deadline is within 3 hours of the task received

Now if I use the attach expression it adds up 3 hour but not working hours

You will see the high lighted data is wrong as it should only give you the deadline the next day

Hope this helps. Qlikview model to big to attach

This new task info is logged on to our system and data then gets pulled to Qlikview

Employee
Employee

Re: How to add 3 working hours in a expression

Hi Sharette

I have the feeling you need to include some more logic in your expression, by using if statements to explain what happens if your value exceeds working hours values.

Something like:

if ((HourValue+3')<5, HourValue+3, day+1 AND HourValue-6).

Hope this helps!

Agis

Not applicable

Re: How to add 3 working hours in a expression

deadline2.JPG.jpg

Dear Agis,

Thank you for the info,

I did try this but however does not work, can you explain why the -6? above is the details that pulls through from this expression

And if I want to exclude weekends do I then bring in networkdays in some where?

Thank you

Sharette

Employee
Employee

Re: How to add 3 working hours in a expression

Hi Sharette

From your sample, I can see that for some reason it cannot calculate correctly the date function, that's why you're getting the 29-Dec-99 date, which basically is the first date for QlikView (Day 0).

My though was that if you have a new task at 15:00, then it should be completed by 9:00 (15-6) next day (Date+1).

That's why I used the -6 there.

For your weekends, you have to add some more logic there, with some if conditions that will check if weekday(Date) is 5 or 6 (Saturday or Sunday) and act accordingly.

Could you please send me your expression which gives you the above results?

Not applicable

Re: How to add 3 working hours in a expression

Deadline expression.JPG.jpg

Dear Agis,

Please see attach expression

Thank you

Sharette

Employee
Employee

Re: How to add 3 working hours in a expression

can you please try this and let me know if it works?

=if(

     hour(time([SQRRfCD])+3)<17,

          timestamp(num([SQRRfCD]+0.125)),

          timestamp(num([SQRRfCD]+0.75))

     )

Regards

Agis

Not applicable

Re: How to add 3 working hours in a expression

Dear Agis,

Thank you so much, this is working great,

Regards

Sharette

Not applicable

Re: How to add 3 working hours in a expression

Dear Agis,

I wonder if you can help me with the following on this same expression

I am now trying to add 11 working hours and it is not calculating correct and I am also trying to exclude the weekends with the if weekday = 5 then add more hours

Example 1 : adding 11 working hours

I am still using the same expression, please see below

Date = 01-Sept-14 17:00 pm

=if(hour(time([Date]) + 0.4583) < 17,

timestamp(num([Date] + 0.4583)),

timestamp(num([Date] + 1.0833)))))


Date expression gives me is 02-Sept-14 03:59 am

Correct answer should be 03-Sept-14 11:00 am

Example 2: exclude weekends

Date = 29-Aug-14 17:00 pm

=if(weekday(if(hour(time([Date]) + 0.4583) < 17,

timestamp(num([Date] + 0.4583)),

timestamp(num([Date] + 1.0833)))) = 5,

timestamp(num([Date] + 3.0833)))

Date expression gives me is 01-Sep-14 18:59 pm

Correct answer should be 02-Sep-14 11:00 am

Please can you have a look and advise what I need to do,

Thank you

Community Browser