Number of NetDays since last event

Steve Moss

Hi,

Looking to calculate the number of netdays since an event was recorded.

I have a {Date of Incident} column and a {Classification} column

 

Looking for the number of days since a specific classification was reported i.e. {Classification}, ="Lost Time Injury".

 

Want to be able to report this as a Key Indicator of No. Days since Last LTI =  ?

 

Regards

 

Steve

Mike Wilday

Try something like this: =NETDAYS(MAX(COLLECT([Date of Incident]:[Date of Incident], Classification:Classification, ="Lost Time Injury")), TODAY())

See Screen shot to view results.

Mike Wilday

Hi Steve, did this solution work for you? 

Steve Moss

Hi there,

No not exactly.

I am trying to reference on a dashboard the number of days since an event took place. The register is closed data so I don't want to add anything there.

Basically will need to use Cross Sheet References to calculate the number of days since an event took place from today.

 

In a data sheet for the dashboard will just have 2 columns

Col 1 - Net Days Since

Col 2 - Classification

In Col 2 -  I will have i.e.

Row 1 - Lost Time Injury

Row 2 - Medical Treatment Injury

Row 3 - First Aid Injury

In Col 1 against each row will be the result of the calculation of the number of Net Days since an event of  the Classification i.e. "Lost Time Injury" took place.

 

I only need I feel those 2 Columns of Data in the Master Sheet to work this out. i.e. [Date of Incident], and [Classification].

 

This figure will load onto a Dashboard

Kind Regards

Steve

Mike Wilday

I think we can still get this to work for you as you'd like it too... try this... 

Same Sheet Formula

=NETDAYS(MAX(COLLECT([Date of Incident]:[Date of Incident], Classification:Classification, ="Lost Time Injury")), TODAY())

Cross Reference Formula | Add this to column1 of the rollup sheet. You're going the bold part references the same sheet and whatever classification is located next to the formula. Delete the parts in the {brackects} one at at time and recreate the cross-sheet reference. 

=NETDAYS(MAX(COLLECT({Cross sheet Date of Incident Reference}, {Cross Sheet reference of Classification}, =Classification@row)), Today())

You just need to create those cross-references... watch my screencast to find out how! 

https://www.screencast.com/t/Z6rNCuguNXaF

Steve Moss

Thanks Mike,

That work perfectly.

 

Kind Regards

Steve

Mike Wilday

Awesome. Glad I could help you out.