"Days since last accident" type summary

[Gunnell]

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:

DATE	EVENT DURATION (MINUTES)
5 days ago	1
5 days ago	4
4 days ago	0
3 days ago	3
3 days ago	8
3 days ago	10
2 days ago	3
Yesterday	1
Yesterday	6
Today	6
Today	4

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?

[Databaser · Accepted solution]

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]

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