How to Calculate Floating Beds for the AHAR
No, not that kind of floating!
Hopefully this post will be obsolete for those using ServicePoint in about a week or so, but if you're confused about what floating beds means or you're concerned about your AHAR's beds being 'floated' correctly (in whichever HMIS product you're using), this aims to clarify what it means and how you can do it yourself just in case things are not correct in time for submission (in whichever HMIS product you're using). I will share the formulas that I used in Excel to calculate it and the variables I used to build an ART report in ServicePoint, in case you want to go that way.
"Floating" beds is an.. interesting.. way to describe what is actually going on. It is basically just counting family beds as single if that is the way they were used on a night, or the other way around, if single beds were used as family beds. The trick about it is beds do not get floated at all if there were no beds used in a different way than they were intended. That sounds obvious, but here's how that shakes out:
If on a night, your agency has 8 out of 8 single beds in use and only 3 out of 10 family beds in use, there would be no floating going on. Your family bed count would still be 10 and your single bed count would still be 8. Since all 8 of the single beds were used for singles, you could say that all the beds were used in the way they were intended, even though the family beds weren't in full use.
If that same agency only served 3 singles and 7 family members, you would still not float any beds, because there were no beds being used in a different way than they were intended. Same goes for if they'd served 10 singles and 15 family members- all 8 single beds theoretically served singles and all 10 family beds theoretically served family members. Whether or not they're both over or under is immaterial.
So you will only float beds IF there were beds that were used in a different way than they were intended. In the example above, if on a night, there were 6 singles and 12 family members served in the 8 single beds and 10 family beds, that is a clear case of when beds should be floated. Two single beds were used to house family members.
Is it time for the formulas yet?
In ART, I created one query per PIT night. Then we needed a few variables to get this going:
To count how many clients were served on the night:
ClientCount4IND (the 4 means the 4th PIT night)
1=Count([ClientCounts4].[Entry Exit Client Id]) Where([ClientCounts4].[Entry Exit Group Id]>0)
1=Count([ClientCounts4].[Entry Exit Client Id]) Where (IsNull([ClientCounts4].[Entry Exit Group Id]))
To count how many beds were available on the night:
1=Sum([BedCounts4].[Bed and Unit Inventory HMIS Participating Beds]) 2Where([BedCounts4].[Bed and Unit Inventory Household Type]="Households with at least one adult and one child")
1=Sum([BedCounts4].[Bed and Unit Inventory HMIS Participating Beds]) 2Where([BedCounts4].[Bed and Unit Inventory Household Type]= "Households without children")
To get the number of beds after the floating calculations have been done, I had to back into this step by step:
- Exclude from Floating - checks each provider for which kinds of beds are available by asking if the max (alphabetically) Household Type is the same as the min Household Type within that provider's bed records. If it is, it returns a 1, if not, it returns a 0.
1=If((Max([HH Type]) = "Households with at least one adult and one child" And Min([HH Type]) = "Households with at least one adult and one child") Or (Max([HH Type]) = "Households without children" And Min([HH Type]) = "Households without children"); 1;0)
- ExtraFAM4 and ExtraIND4 - subtracts the number of family beds from the number of family members. This tells us how many extra clients were in each type of bed. It can return a negative number for "too few" clients.
- How Many to Float 4 - this is where we're figuring out how many beds to float, if any. Remember in your math classes about absolute value? It's where you take any number and if it's negative, you make it positive. Positive numbers remain positive. So we compare how many "extra" clients were in each household type, take the smaller number, and that's how many to float!
1=If([Exclude from Floating]=0; If(Abs([ExtraFAM4])>=Abs([ExtraIND4]);Abs([ExtraIND4]); If(Abs([ExtraFAM4])<=Abs([ExtraIND4]);Abs([ExtraFAM4]))))
- Floating4FAM - the final formula, where we basically just put it all together. If we're supposed to exclude this night's count from floating, return the number of beds in the inventory (no floating.) Otherwise, look for cases where there were no "extra" clients at all and don't float anything for those. Also look for cases where both IND and FAM were either negative or positive and don't float anything then either. And FINALLY, if one is positive and the other is negative, then depending on which one's greater, add/subtract the number of beds to float from the proper Household Type's bed count!
1=If([Exclude from Floating]=1; [BedCounts4 FAM]; If([ExtraFAM4]=0 Or [ExtraIND4]=0 Or IsNull([ExtraFAM4]) Or IsNull([ExtraIND4]); [BedCounts4 FAM]; If([ExtraFAM4]<0 And [ExtraIND4]<0; [BedCounts4 FAM]; If([ExtraFAM4]>0 And [ExtraIND4]>0; [BedCounts4 FAM]; If(([ExtraFAM4]>0 And [ExtraIND4]<0)Or([ExtraFAM4]<0 And [ExtraIND4]>0); If([ExtraFAM4]>[ExtraIND4]; [BedCounts4 FAM]+[How Many to Float4]; If([ExtraFAM4]<[ExtraIND4]; [BedCounts4 FAM]-[How Many to Float4])))))))
The problem I am having with the ART report is that while all the bed numbers turn out perfect, it does not total correctly! Which is kind of important. I'll update this blog if I find out what is going on. As a workaround, you can export to Excel and total everything there.
Just to be up front: none of this has been vetted by Bowman or by HUD, so please use this at your own discretion! Your own peril! Test, test, and test it again. Leave comments if you find anything that doesn't make sense or could be improved.
If you're not using ServicePoint, or you'd rather not mess with ART, but you have a way to get your bed and client counts out of your system and into Excel, you could download this Excel file: Floating Beds workbook.
Floating Beds formulas in Excel
You can see above how the table in Excel is set to only show the data for the 4th PIT date. When you unfilter that, it shows 4 rows for each provider, one for each PIT date. Once you enter your client counts and bed counts, the rest is done for you and the totals are at the bottom of the table. You would likely want to make copies of this tab and keep one tab for ES, one for TH, and one for PSH.
Like I explained earlier, I would hope that this blog post will be obsolete by October, at least for ServicePoint folks. If you start getting questions from your liaison about why/how beds were floated, and you're not sure your software is returning the correct numbers, you can always compare it against this logic for a quick check. However please, as I stated before: this has not been vetted by Bowman or by HUD, so use this at your own discretion! Test, test, and test it again. Leave comments if you find anything that doesn't make sense or could be improved.