timestamp time difference

[Anonymous]

All,

i may have bitten of more than i can chew here, but i am after what appears to be a tricky scenario to be resolved.

in my excel file, i have an extract of worklog information from incident tickets. as you will see, there are multiple rows for a single ticket reference (FLD_TICKET).

I have manually edited some data in column R (FLD_DETAILS) to represent specific text that will be used when a ticket is updated. for example, Submit, SLA 1, SLA 2, SLA 3 etc.

What i need to do, is calculate the time difference between each SLA reference for a ticket. So, the time difference between the word 'submit' and SLA 1. Then the time difference between SLA 1 and SLA 2. and so on. up to SLA 5 to ticket resolution (Ticket Resolved).

Because i will be averaging the time difference for a given month, i dont need the results to be aggregated per ticket id, or day.  As long as, when i select the relevant month, it will give me the average time taken to fulfill SLA 1, SLA 2 etc etc

In the QVW, you will see i have set aside 5 text boxes to capture the relevant average times.

I am struggling with the idea of capturing one piece of info from one field, based on a criteria from another field.

I want to use the field FLD_CREATEDATE to perform the time difference.

Any assistance would be appreciated.

Thanks in advance

Aaron

[sunny_talwar]

What is the expected output you want to see for the 5 text boxes you have created?

[Anonymous]

Hi Sunny.

so, in SLA1, i would expect to see the average time between Submit and SLA contained in the FLD_DETAILS column. The time stamp should be taken from the FLD_CREATEDATE column and is the time between the 2 entries for each ticket ID.

for SLA 2, its the time difference between SLA 1, and SLA 2.... and so on.

Ultimately, i want to measure a monthly average for each SLA.

I am also aware that the time stamp is in epoch format (UNIX) - which isnt a problem, as i can take the difference between the 2 time stamps and divide by 60 to give me the seconds, minutes etc.

[sunny_talwar · Accepted solution]

Not sure where exactly are you subtracting the times, but assuming it is done at FLD_TICKETID level, this might work:

SLA 1

=Interval(Avg(Aggr(Only({<FLD_DETAILS = {'SLA 1'}>} FLD_CREATEDATE) - Only({<FLD_DETAILS = {'Submit'}>} FLD_CREATEDATE), FLD_TICKETID))/60, 'D hh:mm')

SLA 2

=Interval(Avg(Aggr(Only({<FLD_DETAILS = {'SLA 2'}>} FLD_CREATEDATE) - Only({<FLD_DETAILS = {'SLA 1'}>} FLD_CREATEDATE), FLD_TICKETID))/60, 'D hh:mm')

SLA 3

=Interval(Avg(Aggr(Only({<FLD_DETAILS = {'SLA 3'}>} FLD_CREATEDATE) - Only({<FLD_DETAILS = {'SLA 2'}>} FLD_CREATEDATE), FLD_TICKETID))/60, 'D hh:mm')

SLA 4

=Interval(Avg(Aggr(Only({<FLD_DETAILS = {'SLA 4'}>} FLD_CREATEDATE) - Only({<FLD_DETAILS = {'SLA 3'}>} FLD_CREATEDATE), FLD_TICKETID))/60, 'D hh:mm')

SLA 5

=Interval(Avg(Aggr(Only({<FLD_DETAILS = {'SLA 5'}>} FLD_CREATEDATE) - Only({<FLD_DETAILS = {'SLA 4'}>} FLD_CREATEDATE), FLD_TICKETID))/60, 'D hh:mm')

When one FLD_TICKETID is selected

When nothing is selected

Now the issue is that there are certain FLD_TICKETID where we don't have all the SLA's for instance this one

How do you plan to handle such a situation?

HTH

Best,

Sunny

[Anonymous]

Hi Sunny,

thanks once again for coming to my rescue.

Regarding the situation with no SLAs, i think this is handled by the fact that we are only reporting against tickets that have gone through the whole SLA life-cycle. All tickets will have a prefix of SLA1, SLA2 etc, so i will only load tickets that are resolved/closed which will contain all timestamps.

Kindest regards

Aaron