For partners that build or integrate commercially available applications and service solutions with the Oracle Cloud Platform
For partners that provide implementation or managed services around Oracle Cloud Applications
Hi Team,
I am struggling to calculate the time difference between incident creation and closed dates by excluding the weekends. here are the columns and function which I am using to calculate date difference date_diff(incidents.closed,incidents.created), but this logic is counting the days including the weekends. As per requirement I need to exclude the weekends and calculate actual business dates. Could you please help me here on this issue.
Thanks & Regards,
Ashok Ganesh
Hi Ashok.
Glad you got it working :-)
When it comes to excluding holidays there is no easy way (at least not, if you want this dynamic). I don't know how skilled you are with PHP? If you are skilled with PHP than it might be easier as this will al have to be done through a custom script.
As the holiday table normally is not accessable in analytics this data will be retrieved through ROQL after which you have access to the data. Have a look at the attached report that will show you how the report is build.
If you are new to custom script and new to PHP than this may proof to be challenging. I have created a 'How to' for beginners in custom script.
A 'How to' on using regular expressions in custom scripts for GDPR
There might be other ways to do this but this is one example (also in the previous attached report) where you would need to repeat this for every holiday.
((DATE_DIFF(sysdate(),incidents.created)/86400) + 1) - ((to_number(date_format(sysdate(),'WW')) - to_number(date_format(incidents.created,'WW')))* 2) - IF(Date_format(incidents.created,'DAY') = 'Sunday',1,0) - IF(Date_format(sysdate(),'DAY') = 'Saturday',1,0) - 1 - if(incidents.created<'2018-05-02' & sysdate()>'2018-05-01',1,0) - if(incidents.created<'2018-04-03' & sysdate()>'2018-04-02',1,0)
To have this dynamic you will most likely have to rebuild your entire report and perform all calculations in PHP (custom script) in the exit tab. Like I said, this is unfortunately going to take some time and programming but you will learn a lot by doing this.
When importing the attached report you will be asked if you would like to import the report without custom script, here you will click No as you do want to import the custom script.
Hi Sebastiaan,
I am able to exclude the holidays in a static way and thanks a lot for your suggestion.
Also I have another observation here, i.e the current logic will excludes weekends and holidays only for the incidents which were raised and closed in the same year.
For example: If any incidents was raised in 2017 and closed in 2018 then I am getting weird results.
It would be great help here and its the final fix which holding me to complete the report.
Here is the expression which is working properly for current year " ((DATE_DIFF(incidents.closed,incidents.created)/86400) + 1) -
((to_number(date_format(incidents.closed,'IW')) - to_number(date_format(incidents.created,'IW')))* 2) -
IF(Date_format(incidents.created,'DAY') = 'Sunday',1,0) - IF(Date_format(incidents.closed,'DAY') = 'Saturday',1,0) "
Thanks in advance...:)
Ashok.
Hi Ashok
You could try to experiment with this expression:
if(date_format(incidents.closed,'YYYY') > '2017',to_date('20171231','YYYYMMDD'),incidents.closed)
What it does is it will check if the incidens.closed year is higher than 2017 and if so, it will use 31st of December 2017 as a date and otherwise it will use the incidens.closed date.
This expression would basically replace every place you are currently using incidents.closed
To have this dynamic you could use:
if(date_format(incidents.closed,'YYYY') > to_char(to_number(date_format(sysdate(),'YYYY')) - 1),to_date(to_char(to_number(date_format(sysdate(),'YYYY')) - 1)||'1231','YYYYMMDD'),incidents.closed)
In a report this would look like this.
Hi Sebastiaan,
I have tried the above expression by incorporating into my logic but its not giving me the proper count. could you please help me here?
Here is the modified logic:
" round(((DATE_DIFF(incidents.initial_soln,incidents.created)/86400) + 1) -
((to_number(date_format(if(date_format(incidents.initial_soln,'YYYY') > to_char(to_number(date_format(sysdate(),'YYYY')) - 1)
,to_date(to_char(to_number(date_format(sysdate(),'YYYY')) - 1)||'1231','YYYYMMDD'),incidents.initial_soln),'IW')) - to_number(date_format(incidents.created,'IW')))* 2) -
IF(Date_format(incidents.created,'DAY') = 'Sunday',1,0) - IF(Date_format(incidents.initial_soln,'DAY') = 'Saturday',1,0),0)
"
Thanks & Regards,
Ashok.
Try this one:
((DATE_DIFF(if(date_format(incidents.closed,'YYYY') > to_char(to_number(date_format(sysdate(),'YYYY')) - 1),to_date(to_char(to_number(date_format(sysdate(),'YYYY')) - 1)||'1231','YYYYMMDD'),incidents.closed),incidents.created)/86400) + 1) - ((to_number(date_format(if(date_format(incidents.closed,'YYYY') > to_char(to_number(date_format(sysdate(),'YYYY')) - 1),to_date(to_char(to_number(date_format(sysdate(),'YYYY')) - 1)||'1231','YYYYMMDD'),incidents.closed),'IW')) - to_number(date_format(incidents.created,'IW')))* 2) - IF(Date_format(incidents.created,'DAY') = 'Sunday',1,0) - IF(Date_format(if(date_format(incidents.closed,'YYYY') > to_char(to_number(date_format(sysdate(),'YYYY')) - 1),to_date(to_char(to_number(date_format(sysdate(),'YYYY')) - 1)||'1231','YYYYMMDD'),incidents.closed),'DAY') = 'Saturday',1,0)
To include the round you can use:
round(((DATE_DIFF(if(date_format(incidents.closed,'YYYY') > to_char(to_number(date_format(sysdate(),'YYYY')) - 1),to_date(to_char(to_number(date_format(sysdate(),'YYYY')) - 1)||'1231','YYYYMMDD'),incidents.closed),incidents.created)/86400) + 1) - ((to_number(date_format(if(date_format(incidents.closed,'YYYY') > to_char(to_number(date_format(sysdate(),'YYYY')) - 1),to_date(to_char(to_number(date_format(sysdate(),'YYYY')) - 1)||'1231','YYYYMMDD'),incidents.closed),'IW')) - to_number(date_format(incidents.created,'IW')))* 2) - IF(Date_format(incidents.created,'DAY') = 'Sunday',1,0) - IF(Date_format(if(date_format(incidents.closed,'YYYY') > to_char(to_number(date_format(sysdate(),'YYYY')) - 1),to_date(to_char(to_number(date_format(sysdate(),'YYYY')) - 1)||'1231','YYYYMMDD'),incidents.closed),'DAY') = 'Saturday',1,0),0)
Hi Sebastiaan,
Its not working for me, please see the attached screenshots.
Thanks,
Ashok.
I think a better way would be to reverse the date_iff so that the calculation reflects the true value. Let me test a little and get back to you :-)
Try this one :-)
((DATE_DIFF(incidents.closed,incidents.created)/86400) + 1) - if(date_format(incidents.closed,'YYYY') > to_char(to_number(date_format(sysdate(),'YYYY')) - 1),((to_number(date_format(incidents.created,'IW')) - 52)* 2),((to_number(date_format(incidents.closed,'IW')) - to_number(date_format(incidents.created,'IW')))* 2)) - IF(Date_format(incidents.created,'DAY') = 'Sunday',1,0) - IF(Date_format(incidents.closed,'DAY') = 'Saturday',1,0)
No Luck with this also. can you see the second record it was created on 27th Dec 2017 and closed on 1st Jan 2018 so the day count should be 4 by excluding weekends, but it is giving the count with weekends too.
Yeah, I saw it...
The solution lays in counting the weeks after new year and multiply this by 2 (Sat + Sun) that will be extracted.
I modified the code (v3). It looks correct now :-)
((DATE_DIFF(incidents.closed,incidents.created)/86400) + 1) - if(to_number(date_format(incidents.created,'YYYY')) = to_number(date_format(incidents.closed,'YYYY')),((to_number(date_format(incidents.closed,'IW')) - to_number(date_format(incidents.created,'IW')))* 2),if(to_number(date_format(incidents.closed,'YYYY')) > (to_number(date_format(sysdate(),'YYYY')) - 1),(to_number(date_format(incidents.closed,'IW'))*2))) - IF(Date_format(incidents.created,'DAY') = 'Sunday',1,0) - IF(Date_format(incidents.closed,'DAY') = 'Saturday',1,0)
Code explained: If the incident was created in the same year as it was closed, it will perform the regular calculation (week nr - week nr) to get the total weeks * 2. ELSE IF the incident was closed in the year > current system date year - 1 it will only count the week number the incident was closed * 2 (to get Sat + Sun) and extract this from the total.
Hi Sebastiaan,
Still no luck..Its failing to current year incidents, I have attached report definition please verify once. I will try from my end by manipulating the logic.
Thanks,
Ashok.
Your report contained an older code.
Try the the one in the previous post :-)
((DATE_DIFF(incidents.closed,incidents.created)/86400) + 1) - if(to_number(date_format(incidents.created,'YYYY')) = to_number(date_format(incidents.closed,'YYYY')),((to_number(date_format(incidents.closed,'IW')) - to_number(date_format(incidents.created,'IW')))* 2),if(to_number(date_format(incidents.closed,'YYYY')) > (to_number(date_format(sysdate(),'YYYY')) - 1),(to_number(date_format(incidents.closed,'IW'))*2))) - IF(Date_format(incidents.created,'DAY') = 'Sunday',1,0) - IF(Date_format(incidents.closed,'DAY') = 'Saturday',1,0)
Hi Sebastiaan,
I am close to the results now able to get the proper results for current year incidents but for previous year incidents it is giving 3 days extra for each incident. Please verify the highlighted incidents below.
Thanks,
Ashok.
No what is happening is that January 19th is in week 3 and than it would count that as 3 x 2 days. In other words 6 days extraction
That is the difference between using IW & WW
I will try on this and come back to you Sebastiaan.
Thanks & Regards,
Ashok.
Hi Sebastiaan,
I have tried by putting IW and WW, but when I used WW it is giving more count than IW and when I use IW it is not giving exact count. I have attached the both the files here please go through once.
Also I am getting Negative (-) for few records could you please explain why its giving negative count?
Thanks,
Ashok.
Hi Ashok.
The negative numbers are most likely incidents created on a Sunday or closed on a Saturday. If the previous calculation returned a 0 extracting 1 would become -1
- IF(Date_format(incidents.created,'DAY') = 'Sunday',1,0) - IF(Date_format(incidents.closed,'DAY') = 'Saturday',1,0)
Understood, could you help me on the day count of the incidents which were created previous year and closed in current year.
Thanks,
Ashok.
Why not use the rel_date_diff aproach Ashok, it will make things so much easier for you (and me)
It will take care of your holidays and weekends, everything according to your SLA settings
See attached report (and previous post December 17)
You only need to specify your opening hours In the Process tab (which in the attached report is set to 24 as my client is open 24H)
// 3600 seconds in an hour
// 8 hour workday (change the code if you have different workdays specified)$sec = 8 * 3600;
$rows[0][4]->val = $rows[0][3]->val / $sec;
Hi Sebastiaan,
This looks helpful for me but I am getting different results when I am using rel_date_diff(incidents.closed,incidents.created,incidents.rr_id)
can you suggest how to proceed on this?
Thanks,
Ashok.
Ah, yes. That is because you have formatted your column into a time format. You should specify this as decimal type as the conversion is done through PHP (custom script). If you have difficulties getting this to work than just upload your desired report and specify where you want to display this and I will configure it for you. Right now I'm leaving the house but I will be back in a couple of hours.
Download and import my report to see how it works :-)
Here is my report definition. I will also follow your example and try to configure.
Thanks,
Ashok.
Hi Sebastiaan,
I ran your report but still its giving me the improper results. Just take a look at the screenshot. Also while I am trying to import your report I got a pop up that custom scripts may not work after import. I just clicked on "No" and imported the report but results are not correct.
Did you adjust the opening hours in the process tab?
// 3600 seconds in an hour
// 8 hour workday (change the code if you have different workdays specified)$sec = 8 * 3600;
$rows[0][4]->val = $rows[0][3]->val / $sec;
How many hours do you have a working day? Than I can adjust this in your report (almost done)
Attached is your report with the following custom script (adjusted to a 10 hour workday)
Code:
// 3600 seconds in an hour
// 10 hour workday (change the code if you have different workdays specified)$sec = 10 * 3600; //10 hours
$initial = $rows[0][0]->val / $sec; // rel_date_diff Initial solved
$closed = $rows[0][1]->val / $sec; // rel_date_diff Closed$rows[0][10]->val = round($initial); // Round
$rows[0][11]->val = round($closed); // Round$rows[0][10]->val = preg_replace("/(-\d+)/", "", $rows[0][10]->val); // Remove negative numbers (when there is no initial solved date)
$rows[0][11]->val = preg_replace("/(-\d+)/", "", $rows[0][11]->val); // Remove negative numbers (when there is no closed date)
As I don't have your custom field I created this into a comment (just remove the single quotes ' ' from the source column)
'incidents.c$incident_source'
Hi Sebastiaan,
Its working now .
I am so much glad and thankful to your help on this, I have learnt so many things from you. I will keep on testing the report in different ways and give the updates.
If things are proper then I will make the question as answered and we will keep in touch on further issues
Thanks & Regards,
Ashok.
Glad I could help Ashok
Best wishes for 2019 and don't forget to select a best answer so the topic will be marked as resolved (shrinks the list)
There is one more thing that I had ask regarding the no of hours. is it possible to calculate no of hours in the same way how we had calculated days?
Absolutely :-)
Instead of calculating the seconds during your 10 opening hours:
$sec = 10 * 3600; //10 hours
You would just devide it by the seconds in 1 hour
$sec = 3600; //1 hour in seconds
The rel_date_diff in seconds would than be devided by the seconds in an hour giving you the hours.
My advice when you are new to custom script is to follow my little tutorial, it will get you started in custom scripts which are easier than you may think :-)
It will also teach you how the columns are counted. Hidden columns give you trouble that is why I simply add them and adjust the width so you don't notice them.
Please share me the tutorial it will help me a lot to understand more about custom scripts. :)
I am actually trying to add "Initial TAT Hours" and "Final TAT Hours" columns into the report. So I have modified the query and try to import the file but it thrown the bellow error.
Its the final issue I have could you take a look into it once?
Regards,
Ashok.
Got it. Thanks for the help Sebastiaan.
I am going to make it answered the question :)
Thanks & Regards,
Ashok.