Help

"Days since last accident" type summary

Topic Labels: Extensions
Solved
Jump to Solution
327 2
cancel
Showing results for 
Search instead for 
Did you mean: 
Gunnell
5 - Automation Enthusiast
5 - Automation Enthusiast

Do you know how manufacturing facilities have a "days since last accident/injury/whatever" sign? I need to create something like that on a dashboard. I was hoping to be able to use the simple "summary" extension but I can't seem to figure out how to do it.

I have a table that tracks events and their corresponding dates. Events happen almost every day. Every once in a while, there is a day that has 0 events. Each event also has a time duration (minutes). On days with no events at all, a record is still created (via automation) for charting purposes, and the field that shows the duration of the event will show 0. 

I'd like to be able to display the number of days since the date when there were no events. 

Example:

DATEEVENT DURATION (MINUTES)
5 days ago1
5 days ago4
4 days ago0
3 days ago3
3 days ago8
3 days ago10
2 days ago3
Yesterday1
Yesterday6
Today6
Today4

So if you view this summary extension block "today", it would show "4" because we've gone 4 days without seeing a 0.

How can I do this?

1 Solution

Accepted Solutions
Databaser
12 - Earth
12 - Earth

You could link all of your records to 1 record in another table. Then use a rollup field in that new table that looks at your date field and uses MAX(values), which gives you the most recent date. Use the "only include linked records from the table x that meet certain conditions" and set "event duration = 0". In that same new table with the 1 record, create a formula field with DATETIME_DIFF(TODAY(), {your rollup field in the same table}, "days")

That should work. 

See Solution in Thread

2 Replies 2
Databaser
12 - Earth
12 - Earth

You could link all of your records to 1 record in another table. Then use a rollup field in that new table that looks at your date field and uses MAX(values), which gives you the most recent date. Use the "only include linked records from the table x that meet certain conditions" and set "event duration = 0". In that same new table with the 1 record, create a formula field with DATETIME_DIFF(TODAY(), {your rollup field in the same table}, "days")

That should work. 

Gunnell
5 - Automation Enthusiast
5 - Automation Enthusiast

That'll do it, thanks. I also created an automation that links that record on the new Table to "yesterday's" record(s) at midnight each night - the same time another automation creates the new record for "today".