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.
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.
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.