IF with MAX(COLLECT issue

Van Keuren, Jeremy

Hello! I am stumped by a problem I'm working on. Part of my work is to assemble a dashboard that shows the maximum number of occupied beds at several homeless shelters in a given period of time during the previous night. Supervisors at each shelter, hour to hour, complete a survey that asks the number of occupied beds ([Occupied]). Each shelter has its own name ([Shelter Name]). So, a submitted survey adds a line to the top of the

grid that tells me the shelter name, how many occupied beds, and a timestamp (which I converted to military time, but I don't think that matters here). If the survey is submitted during the period I want to monitor, there is a checkbox column ([PeriodConfirm]) that is automatically checked.

I have a checkbox column ([Nighttime Peak]) that SHOULD be checked ("true") if the number in [Occupied] is the highest number that ALSO has [PeriodConfirm] checked. The formula is:

=IF(Occupied@row = MAX(COLLECT(Occupied:Occupied, [Shelter Name]:[Shelter Name], =[Shelter Name]@row, PeriodConfirm:PeriodConfirm, true)), 1, 0)

Here's the problem: it's not working right, and I can't figure out why. The [Nighttime Peak] column is sometimes checked even if [PeriodConfirm] is not. Any idea why?

Genevieve P.

Hi @Van Keuren, Jeremy

Try adding in the checkbox rule for this row as part of the IF statement as well:

=IF(AND(PeriodConfirm@row = 1, Occupied@row = MAX(COLLECT(Occupied:Occupied, [Shelter Name]:[Shelter Name], [Shelter Name]@row, PeriodConfirm:PeriodConfirm, 1))), 1, 0)

That way, even if the Occupied data in that row is the same as a MAX Occupied row somewhere else where the PeriodConfirm is checked, it still has to be checked in this row as well.

Let me know if that helped!

Cheers,

Genevieve