cancel
Showing results for
Did you mean:

# Alteryx designer Discussions

## Looping for Alteryx data

Atom

Hello guys

I am trying to allocate a merchant's monthly total payment to the different operating sections of this merchant in that month.

The allocation should be according to the remaining balance of each section.

The allocation formula is [payment 1= remaining balance 1/ (remaining balance 1+ remaining balance 2)];

To calculate the next month’s balance, I would need to use [this month’s balance- this month’s payment].

Thus I would need to create a loop to use last month’s balance and payment to get this month’s balance; and use this month’s payment to get this month’s balance, and etc.

The original data would look like this

 Payment Date Section #Operating in Month Merchant Overall Monthly Payment Starting Balance # in Series Section Monthly Payment Section Balance_ Month Start Jan-17 1 2 100 600 1 60 600 Jan-17 2 2 100 400 1 40 400 Feb-17 1 2 180 600 2 Feb-17 2 2 180 400 2 Mar-17 1 2 150 600 3 Mar-17 2 2 150 400 3 Apr-17 1 3 300 600 4 Apr-17 2 3 300 400 4 Apr-17 3 3 300 400 1

and the calculated result for the first month should be

 A B C Payment Date Section #Operating in Month Merchant Overall Monthly Payment Starting Balance # in Series Section Monthly Payment Section Balance_ Month Start 1 17-Jan 1 2 100 600 1 60 600 2 17-Jan 2 2 100 400 1 40 400 3 17-Feb 1 2 180 600 2 108 540 4 17-Feb 2 2 180 400 2 72 360 5 17-Mar 1 2 150 600 3 =A5*C5/(C5+C6) =C3-B3 6 17-Mar 2 2 150 400 3 =A6*C6/(C5+C6) =C4-B4 7 17-Apr 1 3 300 600 4 =A7*C7/(C7+C8+C9) =C5-B5 8 17-Apr 2 3 300 400 4 =A8*C8/(C7+C8+C9) =C6-B6 9 17-Apr 3 3 300 400 1 =A9*C9/(C7+C8+C9) =400 (first payment in its own series)

The list would go on....

Is there any suggestion? Should I use macro to do the looping?

Thank you

Alteryx Certified Partner

If the data is consistent (i.e. there are only 2 sections and they are present each month) I'd suggest using the multi-row formula. So if you know you are always going to be using the previous month's balance for the same section number, you will always be looking upwards 2 rows.

You could also sort the data by section then date to group the sections together to make working out the multi-row formula easier

Multi-row formula also has the 'group by' option which is quite useful

Atom

However, I would need to prepare for situation with three or more sections as well.

Also, because I would need to calculate monthly balance first from the previous month, and then calculate payment allocation from this month's balance, and then calculate next month's balance from this month's payment and balance.

Thus a multi-row tool would not be able to complete the looping calculation for my problem.

Do you think batch macro is the way to go?

Atom

@Hugo wrote:

If the data is consistent (i.e. there are only 2 sections and they are present each month) I'd suggest using the multi-row formula. So if you know you are always going to be using the previous month's balance for the same section number, you will always be looking upwards 2 rows.

You could also sort the data by section then date to group the sections together to make working out the multi-row formula easier

Multi-row formula also has the 'group by' option which is quite useful

Thanks for the response

Labels