Forum Discussion
Call duration calculation across multiple entries
Hi,
We are wanting to calculate the duration between call transfers. We have the open and close time for a call as well as the time that the transfers take place, but need a way to calculate the time between one activity and the previous activity, for example:
| Call # | Call Open | Call Closed | Activity Type | Activity Time | WE NEED THIS ONE |
| 123 | 21/06/2018 10:00 | 21/06/2018 12:00 | Transfer | 21/06/2018 10:10 | 10 mins (Current Activity Time - Call Open) |
| 123 | 21/06/2018 10:00 | 21/06/2018 12:00 | Transfer | 21/06/2018 11:30 | 1hour 20 mins (Previous Activity Time - Current Activity Time) |
| 123 | 21/06/2018 10:00 | 21/06/2018 12:00 | Close | 21/06/2018 12:00 | 30 mins (Previous Activity Time - Current Activity Time) |
Essentially the calculation would be the time between the current row and the previous row, where if there is no previous row, use the Call Open instead.
Your help in this VERY much appreciated! :)
Thank you!
This helped me work out the solution.
To get the Index:
Index.TEST =
RANKX (
FILTER (
'Activities Analyst',
EARLIER ( 'Activities Analyst'[Ticket #] ) = 'Activities Analyst'[Ticket #]
&& ('Activities Analyst'[Type] = "Transfer" || 'Activities Analyst'[Type] = "Close")
),
'Activities Analyst'[System Time],
,
ASC
)To get the final duration per transfer/activity
Prev.Duration.TotalHours= var CurrentIndex = 'Activities Analyst'[Index.TEST]
var CurrentTicket = 'Activities Analyst'[Ticket #]
var CurrentdDuration = 'Activities Analyst'[Duration.TotalHours]
return
CALCULATE(CurrentdDuration-sum('Activities Analyst'[Duration.TotalHours]),FILTER(ALL('Activities Analyst'),'Activities Analyst'[Ticket #]=CurrentTicket && 'Activities Analyst'[Index.TEST]=CurrentIndex-1 && ('Activities Analyst'[Type] = "Transfer" || 'Activities Analyst'[Type] = "Initial")))Works a treat and combines the last two columns into one.
2 Replies
Hello Anonymous,
My solution will get you close to what you want.
In the Query Editor, using the table:
Call # Call Open Call Closed Activity Type Activity Time 123 6/21/18 10:00 AM 6/21/18 12:00 PM Transfer 6/21/18 10:10 AM 123 6/21/18 10:00 AM 6/21/18 12:00 PM Transfer 6/21/18 11:30 AM 123 6/21/18 10:00 AM 6/21/18 12:00 PM Close 6/21/18 12:00 PM
Add Custom Column for durationMinutes, ex. uses minutesDuration.TotalMinutes ( [Activity Time]-[Call Open] )
Add Index using user interface.
Add another Custom Column to get previous value, use try...otherwise for the error you may get
try #"Added Index" {[Index]-2}[durationMintues] otherwise 0Add another Custom Column to do the subtraction.
[durationMintues] - [Custom]
Should result in:
Thank you!
This helped me work out the solution.
To get the Index:
Index.TEST =
RANKX (
FILTER (
'Activities Analyst',
EARLIER ( 'Activities Analyst'[Ticket #] ) = 'Activities Analyst'[Ticket #]
&& ('Activities Analyst'[Type] = "Transfer" || 'Activities Analyst'[Type] = "Close")
),
'Activities Analyst'[System Time],
,
ASC
)To get the final duration per transfer/activity
Prev.Duration.TotalHours= var CurrentIndex = 'Activities Analyst'[Index.TEST]
var CurrentTicket = 'Activities Analyst'[Ticket #]
var CurrentdDuration = 'Activities Analyst'[Duration.TotalHours]
return
CALCULATE(CurrentdDuration-sum('Activities Analyst'[Duration.TotalHours]),FILTER(ALL('Activities Analyst'),'Activities Analyst'[Ticket #]=CurrentTicket && 'Activities Analyst'[Index.TEST]=CurrentIndex-1 && ('Activities Analyst'[Type] = "Transfer" || 'Activities Analyst'[Type] = "Initial")))Works a treat and combines the last two columns into one.
