Help with Beast mode to calculate time in tier
Reaching out to the community in hopes that someone has dealt with the use case I'm struggling with.
Our support organization operates in a tiered model (Tier 1, Tier 2, and Tier 3). I'm in need of assistance writing a beast mode to calculate the amount of time a ticket sat in each of the tiers.
Using the above table, I understand the calculation to be as outlined below. However, writing it in beast mode is not as easy, at least I'm not finding it to be.
Time in Tier 1
- 2022-03-01T07:37:11 (TICKET_HISTORY_UPDATED_AT) - 2022-03-01T06:54:29 (CREATED_AT)
- TICKET_HISTORY_UPDATED_AT aligned to Tier 2 as this is when the ticket was assigned to group
- Total time in Tier 1 would be 43 minutes
Time in Tier 2
- 2022-03-08T10:22:34 (TICKET_HISTORY_UPDATED_AT) - 2022-03-01T07:37:11 (TICKET_HISTORY_UPDATED_AT)
- TICKET_HISTORY_UPDATED_AT aligned to Tier 2 as this is when the ticket was assigned to group
- TICKET_HISTORY_UPDATED_AT aligned to Tier 3 as this is when the ticket was assigned to group
- Total time in Tier 2 would be 165 minutes
Comments
-
I would suggest looking at the TIMEDIFF function and the UNIX_TIMESTAMP function. The TIMEDIFF function is pretty straightforward:
TIMEDIFF
Returns the difference between values in two date/time columns, expressed as a time value.
TIMEDIFF('Time 1','Time 2')
The UNIX_TIMESTAMP function will require a little bit more work. Grant Smith has a nice breakdown of it here: https://dojo.domo.com/discussion/52682/domo-ideas-conference-beast-modes-time-difference-formatting#latest
**Check out my Domo Tips & Tricks Videos
**Make sure to any users posts that helped you.
**Please mark as accepted the ones who solved your issue.0 -
@MarkSnodgrass I've looked into TIMEDIFF function, but as you mentioned, it calculates based on 2 different columns. In my example above, Time in Tier 2, the timestamps fall within the same column and this is where I'm struggling to construct a formula.
0 -
Ahh... It was hard to tell from the screenshot if you need to access info from two different rows. In this case, You can still use the TIMEDIFF function, but you are going to need to do some work in Magic ETL first. You would need to utilize the rank and window tile and then use the Lag or Lead function so that you can add the data from a different row as an additional column. After you have that added, you can then use the timediff function. This KB article breaks down the Rank & window tile nicely if you haven't used it before. https://domohelp.domo.com/hc/en-us/articles/360044876094-New-Magic-ETL-Tiles-Aggregate#3.
**Check out my Domo Tips & Tricks Videos
**Make sure to any users posts that helped you.
**Please mark as accepted the ones who solved your issue.2 -
That worked, thanks @MarkSnodgrass
1 -
@MarkSnodgrass thanks again for your guidance. While I was able to calculate time difference in business hours, I've been unable to accurately validate my data, as I never accounted for weekends. This is what I was able to put construct thus far:
CASE
WHEN `events rank` = 1 THEN
ROUND(((UNIX_TIMESTAMP(`Tier Transition`) - UNIX_TIMESTAMP(`Adjusted_Created_At`))/3600),2) - (DATEDIFF(`Tier Transition`,`Adjusted_Created_At`)*9)
ELSE
ROUND(((UNIX_TIMESTAMP(`Tier Transition`) - UNIX_TIMESTAMP(`Adjusted_Ticket_History_Update`))/3600),2) - (DATEDIFF(`Tier Transition`,`Adjusted_Ticket_History_Update`)*9)
END
How would I be able to exclude weekends in the above calculation?
0
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 298 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.9K Transform
- 100 SQL DataFlows
- 616 Datasets
- 2.2K Magic ETL
- 3.8K Visualize
- 2.5K Charting
- 729 Beast Mode
- 54 App Studio
- 40 Variables
- 678 Automate
- 173 Apps
- 451 APIs & Domo Developer
- 46 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
- 33 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 108 Community Announcements
- 4.8K Archive