topic Re: How to add 3 working hours in a expression in Qlik Learning Discussions

How to add 3 working hours in a expression

Wed, 27 Aug 2014 10:03:25 GMT

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

Re: How to add 3 working hours in a expression

MK_QSL — Wed, 27 Aug 2014 10:05:24 GMT

Provide little sample data...plz

Re: How to add 3 working hours in a expression

Wed, 27 Aug 2014 11:31:39 GMT

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

Re: How to add 3 working hours in a expression

Agis-Kalogiannis — Wed, 27 Aug 2014 13:19:51 GMT

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

Re: How to add 3 working hours in a expression

Thu, 28 Aug 2014 06:22:12 GMT

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

Re: How to add 3 working hours in a expression

Agis-Kalogiannis — Fri, 29 Aug 2014 19:09:47 GMT

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?

Re: How to add 3 working hours in a expression

Tue, 02 Sep 2014 13:08:42 GMT

Dear Agis,

Please see attach expression

Thank you

Sharette

Re: How to add 3 working hours in a expression

Agis-Kalogiannis — Tue, 02 Sep 2014 15:44:49 GMT

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

Re: How to add 3 working hours in a expression

Wed, 03 Sep 2014 14:40:50 GMT

Dear Agis,

Thank you so much, this is working great,

Regards

Sharette

Re: How to add 3 working hours in a expression

Tue, 09 Sep 2014 10:14:10 GMT

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

Re: How to add 3 working hours in a expression

Tue, 09 Sep 2014 15:20:29 GMT

§ 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() + 0.4583) < 17,

timestamp(num( + 0.4583)),

timestamp(num( + 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() + 0.4583) < 17,

timestamp(num( + 0.4583)),

timestamp(num( + 1.0833)))) = 5,

timestamp(num( + 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

Kind regards,

Sharette

Re: How to add 3 working hours in a expression

Agis-Kalogiannis — Thu, 11 Sep 2014 23:45:04 GMT

Hi Sharette... Could you please try the following?

CASE A:

=if(

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

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

timestamp(num([SQRRfCD]+1.75))

)

Now, could you please apply the new changes to you weekend expression and check if you are getting correct results?

Regards

Agis