community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.

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 DateSection#Operating in MonthMerchant Overall Monthly PaymentStarting Balance# in Series

Section 

Monthly Payment

Section Balance_ Month Start
Jan-1712100600160600
Jan-1722100400140400
Feb-17121806002  
Feb-17221804002  
Mar-17121506003  
Mar-17221504003  
Apr-1713300600  
Apr-1723300400  
Apr-1733300400  

 

and the calculated result for the first month should be 

    A  BC
 Payment DateSection#Operating in MonthMerchant Overall Monthly PaymentStarting Balance# in SeriesSection Monthly PaymentSection Balance_ Month Start
117-Jan12100600160600
217-Jan22100400140400
317-Feb121806002108540
417-Feb22180400272360
517-Mar12150600=A5*C5/(C5+C6)=C3-B3
617-Mar22150400=A6*C6/(C5+C6)=C4-B4
717-Apr13300600=A7*C7/(C7+C8+C9) =C5-B5
817-Apr23300400=A8*C8/(C7+C8+C9) =C6-B6 
917-Apr33300400=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

Thank you for your response.

 

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