Help With Beast Mode Calculation
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.
Answers
-
@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.
0 -
I am new to ETL but I will give this a try. Thanks Mhouston
0 -
@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)
“There is a superhero in all of us, we just need the courage to put on the cape.” -Superman1 -
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.
0
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 296 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.9K Transform
- 100 SQL DataFlows
- 614 Datasets
- 2.2K Magic ETL
- 3.8K Visualize
- 2.5K Charting
- 729 Beast Mode
- 53 App Studio
- 40 Variables
- 677 Automate
- 173 Apps
- 451 APIs & Domo Developer
- 45 Workflows
- 8 DomoAI
- 34 Predict
- 14 Jupyter Workspaces
- 20 R & Python Tiles
- 394 Distribute
- 113 Domo Everywhere
- 275 Scheduled Reports
- 6 Software Integrations
- 121 Manage
- 118 Governance & Security
- Domo Community Gallery
- 32 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 108 Community Announcements
- 4.8K Archive