11 Replies Latest reply: Sep 15, 2014 5:55 AM by Agis Kalogiannis RSS

    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

        • Re: How to add 3 working hours in a expression
          Manish Kachhia

          Provide little sample data...plz

            • 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

                • Re: How to add 3 working hours in a expression
                  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

                    • 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

                        • Re: How to add 3 working hours in a expression
                          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?

                            • Re: How to add 3 working hours in a expression

                              Deadline expression.JPG.jpg

                               

                              Dear Agis,

                               

                              Please see attach expression

                               

                              Thank you

                              Sharette

                                • Re: How to add 3 working hours in a expression
                                  Agis Kalogiannis

                                  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

                                      Dear Agis,

                                       

                                      Thank you so much, this is working great,

                                       

                                      Regards

                                      Sharette

                                        • 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

                                        • 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() + 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