Formula Tool to Populate Columns by Splitting an amount across columns using a limit

[Siharmer]

Hi Everyone,

Relatively new to Alteryx so please go easy! Just thought I'd see if someone here can help me with trying to populate 5 columns based on the value of a field.

Essentially I have a total amount eg. 600 and I want to split this over 5 columns (Week 1, Week 2,  Week 3,  Week 4,  Carry Over) starting at Week 1 and moving to week 5. However the maximum amount each week (Weeks 1-4) can only be 150 and it should fill the columns starting with Week 1. Anything left over would go into Carry Over.

I have included below what the output would look like (in the attached excel), any ideas how I achieve this? I have tried using IF statements but can't get the logic to work properly (I end up with negative values in some of the columns)?

Any help would be greatly appreciated!

Simon

[Qiu · Accepted solution]

@Siharmer 
It turns out to be a very interesting one, though I am not quite happy with my flow, but I think it will work.

Maybe we can submit this as a weekly challenge idea?

[Siharmer]

 Thanks Qiu, that worked perfectly!

[Qiu]

@Siharmer 
Glad to help.
Can I submit this one as weekly challenge idea?

[Siharmer]

Yep, no problem 😁

[Qiu]

Great!

[flying008 · Accepted solution]

Hi, @Siharmer 

Another way like @Qiu  for you :

[Siharmer]

Thanks @flying008 ... also works perfectly!