Help with Beast mode to calculate time in tier

cjimenez

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

MarkSnodgrass

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

cjimenez

@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.

MarkSnodgrass

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.

cjimenez

That worked, thanks @MarkSnodgrass

cjimenez

@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?