Help with Beast mode to calculate time in tier

Options
cjimenez
cjimenez Member
in Magic ETL

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.

Ticket by Tier.png

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

  • MarkSnodgrass
    MarkSnodgrass Coach

    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 <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • cjimenez
    cjimenez Member

    @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
    MarkSnodgrass Coach

    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 <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • cjimenez
    cjimenez Member

    That worked, thanks @MarkSnodgrass

  • cjimenez
    cjimenez Member

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

Categories