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

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

Van Keuren, Jeremy

That done did it, alright. Thank you!