IF with MAX(COLLECT issue

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([email protected] = 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?

Best Answer

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    Hi @Van Keuren, Jeremy

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

    =IF(AND([email protected] = 1, [email protected] = 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

Answers

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    Hi @Van Keuren, Jeremy

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

    =IF(AND([email protected] = 1, O[email protected] = 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

  • That done did it, alright. Thank you!