Calculate bedroom requirements of a household

[jagjit_singh]

Hi,

I'm trying to calculate the housing utilisation and have manually filled in the bedroom requirement column which needs to be calculated using alteryx based on the below. My logic is to assign a value of 1 or 0 then SUM bedroom requirements by household_ID. I can then compare total bedroom requirement value against the number of bedrooms in the property.

The measure assesses the bedroom requirements of a household by specifying that:

• there should be no more than two persons per bedroom
• children less than five years of age of different sexes may reasonably share a bedroom
• children less than 18 years of age and of the same sex may reasonably share a bedroom
• single household members 18 years and over should have a separate bedroom, as should parents or couples
• A child aged 5-17 years should not share a bedroom with a child under 5 years of the opposite sex
• Single adults 18 years and over and any unpaired children require a separate bedroom

http://www.abs.gov.au/ausstats/abs@.nsf/Lookup/by%20Subject/2900.0~2016~Main%20Features~HOSD%20House...

Thanks

Jag

[NicoleJohnson · Accepted solution]

Hi @jagjit_singh!

Take a look at the attached workflow, I think this will achieve the calculation you're looking for.

Essentially, split the data into two paths, adult vs. child... calculated the number of rooms for adults based on 0 for Partner, 1 for everyone else... then calculated the number of rooms required for children/adolescents using a combination of Summarize Tool, Generate Rows, and Multi-Row formula grouped by Household & Gender. I then joined/unioned the information back together to get the data for each individual along with their bedroom requirement value. Workflow gives me the same results as your test Excel file.

Take a look, let me know if you have any questions!

Cheers,

NJ

[jdunkerley79]

Mostly for interest (and as I havent answered any of @jagjit_singh questions in a while) had a go.

Took a slightly different approcah

- Categorised each row into Head, Other, Partner, Boy, Girl, BoyU5, GirlU5

- Used a Cross Tab to compute the counts of each

- The move any U5 boy or U5 girl to fill the Boy and Girl rooms

- Finally compute bedrooms:

[CountWithNulls_Head] + [CountWithNulls_Other] + MAX(0, [CountWithNulls_Partner] - [CountWithNulls_Head] - [CountWithNulls_Other])
+ CEIL([CountWithNulls_ChildGirl]/2)
+ CEIL([CountWithNulls_ChildBoy]/2)
+ CEIL(([CountWithNulls_ChildBoyU5]+[CountWithNulls_ChildGirlU5])/2)

J

[jagjit_singh]

It's strange that when I download the workflow, I get the one Nicole uploaded. Can you please check the workflow uploaded.

Yeah its an interesting one :)

Thanks J.

Jag

[jdunkerley79]

Doh! Updated with correct workflow.