How to add 3 working hours in a expression

[#1]

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

[MK_QSL]

Provide little sample data...plz

[#3]

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

[Agis-Kalogiannis]

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

[#5]

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

[Agis-Kalogiannis]

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?

[#7]

Dear Agis,

Please see attach expression

Thank you

Sharette

[Agis-Kalogiannis · Accepted solution]

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

[#9]

Dear Agis,

Thank you so much, this is working great,

Regards

Sharette

[#10]

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