Can you help me show the status duration, timechart with stack bars in 30min spans?

[proyleJDS]

This could get a little tedious but here goes:

I have call centre data that is giving me the users' statuses, whether they are in a call — or another status, like in coaching or on a break.

I have the start time of the status change and the event time stamp from which I can calculate the duration of the status to determine how long the user was on a call or in a meeting etc.

Here is a typical timeline for a user status over say 2 hours

in a call - 40min
after call work - 10min
in a call - 20min
after call work - 10min
coaching - 20min
break - 20min

Each status that runs longer than a minute will have multiple events. Each one having a timestamp further from the StatusStarttime, so the duration increases until the events finish for that status, this latest event is the event that I grab and plot on a timeline.

it might look a bit like this:

The client would like to see a 100% stacked bar in 30 min increments. As you can see from the chart, there are many events that have durations that cross the 30 min boundaries so the stacked bars rarely add up.

Is there a way to split the events (like the one with the red arrow) with overlapping durations and divide them correctly into the different 30 min time slots?

I told you it was tedious.

[chrisyounger · Accepted solution]

If you have data that logs state change type events you can use logic like this:

Firstly do this to show what the user was doing when we know they changed status:
| timechart span=1m latest(status) as status by employee

Then carry-over the state until they change state again:
|filldown

Then do another timechart with the span you actually care about:
| timechart span=1h count by status

All the best

[cmerriman]

Do you have any data you can provide for us to play with? stripped of any PII?