Calculating Date Time Stamps for duplicate ID's

user053570
user053570 Member
in Magic ETL

I am tasked to create KPIs for how long it takes for an ID to sit in a specific status. Attached is a snippet of sample data for reference.

 

The amount of days for the first "status change time' would be taken from the 'HRC Create date', or when the ID was created, the rest of the changes would be the difference between each status time change and stop after there is a new ID.

 

What would a beast mode look like to calculate the amount of days between each time change per ID #?

 

 

 

Thanks in advance.

Comments

  • MarkSnodgrass
    MarkSnodgrass Coach

    The TIMEDIFF() function will get you the hh:mm:ss value between the two if you are just looking to get the difference between the Status Change Time and the NRC Create Date in a single row.

    Yours would look like this:

    TIMEDIFF(`Status Change Time`,`HRC Create Date`)

    Here's a link to the documentation:

    https://knowledge.domo.com/Visualize/Adding_Cards_to_Domo/KPI_Cards/Transforming_Data_Using_Beast_Mode/02Beast_Mode_Functions_Reference_Guide#Date_and_Time_functions

     

    **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.
  • user053570
    user053570 Member

    Yes i know that is the calculation to find the difference in time, but I am looking to base those calculations off of the specific ID numbers. 

     

    Since there are multiple changes to a specific ID the calculation will be different than just the Timediff function. For the first time change of the ID, the time difference should look exactly how you typed it out, 

    TIMEDIFF(`Status Change Time`,`HRC Create Date`)

    but the other changes should not reference the "HRC Create Date". It should be a difference between the top row's "Status Change Time" and the new row's "Status Change TIme".

     

    This calculation should also start over every time the ID number changes.

     

    Hope this additional information helps, it's a little more complicated than I would have liked!

  • MarkSnodgrass
    MarkSnodgrass Coach

    To get the time between the first entry of one column and the last entry of another for a particular column, you are going to need to use the ETL. You can accomplish by doing a Group By and group by the ID and use the MIN function on the HRC Create Date and the MAX function on the Status Change Time. This would give you one row for each ID with the earliest HRC Create Date for that ID and latest Status Change Time for that ID. 

    You could build a card off of that dataset and then add a drill path to drill into the detailed dataset if you needed to show the individual changes.

     

    Hope this helps.

    **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.
  • user053570
    user053570 Member

    Kind of what I was looking for but not quite. I ended up bringing the data into redshift and wrote sql code to get what I was looking for. Thanks for your help!

Categories