Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
Provide little sample data...plz
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
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
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
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?
Dear Agis,
Please see attach expression
Thank you
Sharette
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
Dear Agis,
Thank you so much, this is working great,
Regards
Sharette
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