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(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?
Best Answer
-
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
Answers
-
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
-
That done did it, alright. Thank you!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- 10.8K Get Help
- 65 Global Discussions
- 69 Industry Talk
- 385 Announcements
- 3.5K Ideas & Feature Requests
- 56 Brandfolder
- 125 Just for fun
- 50 Community Job Board
- 464 Show & Tell
- 40 Member Spotlight
- 44 Power Your Process
- 28 Sponsor X
- 234 Events
- 7.3K Forum Archives
Check out the Formula Handbook template!