Help With Beast Mode Calculation

Goodeman

I have a dataset that has the following Fields

Agent Name - Name of the agent

Agent Status= This is the state the agent was changed to . Available, Unavailable, Idle etc.

EventTime = Equals the timestamp that the agent status changed to

I need to calculate the time the agent was in that state, for example, Agent changed state to "unavailable" at 1:00 PM and then "Available" at 2:00 PM. So I should get back 1 hr as the unavailable time.

What I am struggling with is the eventtime field is all I have so I would need to subtract the time in between unavailable and available.

mhouston

@Goodeman you can do this using rank and window and a formula tile in Magic ETL.

You will want to add a rank and window tile in Magic ETL using the lead function so you can get the nextrow's value for event time (based on my understanding that the event time is the first time your agent hits that status). You would partition on your agent and agent status, and order by the eventtime. Then you can use a formula tile to subtract that event time from current event time to get the difference, which would be your time in the status. You'll need a case in your formula to handle when the next row doens't exist and your time is null (I think you'd just use current time then).

Edited to add I saw that you said beast mode - I think you can do the same thing if you have the functions enabled in beast mode (you may have to reach out to your CSM to enable rank & window in beast mode). But the logic would be the same.

Goodeman

I am new to ETL but I will give this a try. Thanks Mhouston

ST_Superman

@Goodeman - You should be able to get something like this to work, assuming you have the card sorted by agent name and event time:

round(TIME_TO_SEC(TIMEDIFF(lead(`EventTime`, 1) over (partition by `Agent Name` order by `EventTime` asc), `EventTime`))/3600, 1)

Goodeman

Thanks all for your help so far but I think I need to be clearer with what I am requesting. So, I would like to get a report of unavailable time for each agent. See data below. So, I would like to find whenever there is a change in "agent state" to unavailable, I want to take the time in event Timestamp row that corresponds with the state above the unavailable value and minus it from event timestamp that corresponds with the unavailable state to get "total time unavailable". I then need this sorted by Agent. I hope this make more sense.