Calculate time difference from metrics  Explore
I'm trying to achieve results as a difference between 2 calculated metrics. Values calculated and displayed on the below metrics are DATE_DIFF
DATE_DIFF(now(),[Update  Timestamp],"nb_of_seconds")
1. Agent to 2nd (secs)
2. 2nd to Agent (secs)
Want difference value from (12).
Challenge: My customer metric formula returns value in separate rows if the date/month/week/year changes. How can I compute on explore to get the below result.
IF 2ndtoagent (secs) is blank/null/empty and Agentto2nd (secs) is not empty/blank/null
Then get value from next row of same column and call it Value 2
Value(Agentto2nd (secs))  Value 2
2153149  1953239
If I use normal 1  2 function, the value is returned on each row and instead I need an answer
2153149  1953239 = 199910 displayed in any 1 of the rows.
How can this be achieved in explore queries?

A quick help with an explanation would be highly appreciated. Thanks in advance

Palash, because you are showing the update time in your query, you are only going to get 1 of your metrics per line (since there would only be one update per line). If you remove this and just have the Ticket ID or some other grouping column, does it display more as you would like? If not, you may need to expand your attribute calculation to also include more filters. I.e. for the Agent to 2nd, I'm assuming you have some filtering for calculating this (change in group)  can you add that to your calculation so that you can filter to the ticket level and only have 1 line per ticket to get your numbers to subtract from 1 line properly?

Thank you for your response. Yes, I'm using a filter to find specific change in group and retrieve its timestamp. What I want is to calculate time difference between these changes.
So for Ticket 1030863,
I need to calculate the difference in time *EC_EN*  *2nd_line_services*, If i remove the timestamp from the row, we get single row for each ticket and then the calculations are incorrect.

Correct, you will not be able to do the calculations you want if you keep a single update on each row (i.e. showing the update timestamp attribute). You cannot make calculations across rows.
In Explore, you will need to modify your custom attributes and metrics in the query so you only get 1 line per ticket (rather than per update), so both calculations would fall on the same line, then you can do the subtraction. To do this, you just need to expand the criteria in your metrics (or attributes, however you built them). For example, 2ndtoAgent (secs) wouldn't just be the DATEDIFF, it would also be an IF statement for the criteria to identify which update to make this calculation (group ID, etc). This way, when you collapse the rows to just be 1 per ticket, your metrics will still show properly in the single ticket line for both metrics. Basically make the calculation independent of the query criteria  put all the criteria in the metric calculation.
If you need to keep the update column in your output, the only thing I can think to do is to download your query results to Excel and do the calculation you need in Excel (where you can subtract between rows).
Por favor, entrar para comentar.
4 Comentários