Is it possible to calculate difference between 2 date fields and show the result in reports using PA

tyagisu · ‎04-12-2018

Hi,

I have a requirement to show the difference between RCA Delivery Date and Problem Record Open Date from problem record. I need to show the avg of those records with assignment groups in row and months in a column.

Something like this:

Any help would be appreciated.

Thanks in advance,

Su

Dennis R · ‎04-13-2018

A couple of notes:

1) Your calculation calculates days, but your unit displayed is hours. If you want it in hours, you need to change this line:

var hours=function(x,y){return diff(x,y)/(1000 * 60 * 60 * 24);};

To this:

var hours=function(x,y){return diff(x,y)/(1000 * 60 * 60);};

2) Are you sure the calculation is incorrect? Have you calculated what it should be manually?

View solution in original post

Dennis R · ‎04-12-2018

This is relatively simple to accomplish. You'll need to create a scripted average indicator. To do that, look at the Script [pa_scripts] table (Performance Analytics > Automation > Scripts in the navigation pane) and create a new script that will calculate the RCA delivery time.

I don't know what your RCA Delivery Date field name is, but if you want the average time to be in days, your script will look something like this:

(current.u_rca_delivery_date.dateNumericValue() -
        current.opened_at.dateNumericValue()) / (1000 * 60 * 60 * 24);

Name the script something like Problem.RCATime.Days, and then in your indicator on the Source tab, select Average as your aggregate, select the Scripted checkbox, and choose Problem.RCATime.Days as your script.

You can get as fancy with this as you want in your script. For example, if you want to calculate the average business time to deliver an RCA instead of calendar time, just use something like this:

// Substitute the sys_id of your business schedule in cmn_schedule below.        
var schedId = '0123456879abcdef0123456789abcdef';
var busHoursPerDay = 9; // Number of hours in a business day in the schedule

var gsBusiness = new GlideSchedule(schedId);
var dur = gsBusiness.duration(new GlideDateTime(current.opened_at),
        new GlideDateTime(current.u_rca_delivery_date));
dur.getNumericValue() / (1000 * 60 * 60 * busHoursPerDay);

Hope this helps,
--Dennis R

tyagisu · ‎04-12-2018

Hi Dennis,

Thanks for your response.

I am using this script but I am not getting correct data. Please let me know if my script is not correct.

var diff=function(x,y){return y.dateNumericValue() - x.dateNumericValue();};
var hours=function(x,y){return diff(x,y)/(1000 * 60 * 60 * 24);};
hours(current.opened_at,current.u_rca_completed_date);

Thanks,

Su

Dennis R · ‎04-12-2018

What exactly do you mean when you say "I am not getting correct data"? What are you getting, and what are you expecting? Can you get an example of an opened_at/u_rca_completed_date with what you're getting, and what you are expecting?

--Dennis R

tyagisu · ‎04-13-2018

Hi Dennis,

I am getting the same value for all the months.