How to allocate value to group memebers

Hi,

I want to allocate income amount to customers based as below

1) The first adult in the tenancy gets allocated income amount 585. The other adults in the tenancy get allocated 225.

2) Similarly the first child in the tenancy gets allocated income amount 285 and the 2nd, 3rd, etc childern get allocated 95

I have uploaded my sample dataset and desired output here:

https://app.box.com/s/lhexkurnfhitwirl9aut7ff9y4537vy8

Thanks

Jag

Preparation

Accepted answers

tom_montpool

There is probably another way, but I've given you two different options, one using Running Total, another with Tile.

For some reason I can't post the YXZP so you'll have to use the sample dataset you posted.

Sample_Solution.yxmd

All comments

MarqueeCrew

I took a simple approach to this. I created an Allowance table to append to each row. This externalizes the amounts from the program. You could store this outside of the module and call it with updated values in the future. I separate the Adults from the Children and then assign a person number (by Adult vs Child) to each HH member. After I append the values, it is a simple IF THEN statement that assigns the amounts.