Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
What is the expected output you want to see for the 5 text boxes you have created?
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.
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
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