Calculating Date Time Stamps for duplicate ID's

user053570

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.

MarkSnodgrass

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

 

user053570

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

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.

user053570

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!