Forum Discussion
Help with column analysis
I have a table with dispatch data and I need to find the smallest time element when multiple units are on the same call.
If the Incident Number is the same, return the smallest Dispatch to Clear Time in seconds to column BE when Truck 1 annd Engine 2 are on the same call. Sample data attached.
Thanks
you can try this
Column = minx(FILTER(Sheet1,Sheet1[Incident Number]=EARLIER(Sheet1[Incident Number])&&Sheet1[Frontline Unit]=1&&Sheet1[Multi U]>1),Sheet1[Dispatch To Clear (seconds)])+0pls see the attachment below
6 Replies
not clear about your question. How did you get the result in Excel? I didn't see any formula.
Apologies. Here is the sheet with the formula. What I am trying to find is the amount of time multiple units were busy if they are frontline units. Column BD has the calculation. I also cannot figure out how to return just one number per event as I'll need to sum the column. Many thanks!
you can try this
Column = minx(FILTER(Sheet1,Sheet1[Incident Number]=EARLIER(Sheet1[Incident Number])&&Sheet1[Frontline Unit]=1&&Sheet1[Multi U]>1),Sheet1[Dispatch To Clear (seconds)])+0pls see the attachment below
Ryan,
Thanks, that did get the correct output.
you are welcome
Hi Vegonzalez ,
Did ryan_mayu reply solve your problem? If so, please mark it as the correct solution, and point out if the problem persists.
Best regards,
Adamk Kong
