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:
Thanks
Jag
Solved! Go to 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
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
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
Doh! Updated with correct workflow.