Batching iterative macro

[Napster]

Hello, Alteryx community!

I'm quite new to Alteryx. Lately I have been working on a workflow to automate calculation of depreciation of each individual aircraft for each month of the year. I have encountered an issue of circular referencing that is very simple to solve with dynamic referencing when it comes to excel.

I have the following simplified dataset 

Aircraft Type	Aircraft ID	Period start	Period end	Initial cost at start	Accumulated depreciation at  start	Depreciation for period	Initial cost at end	Accumulated depreciation at  end
Airbus A330	VP-ABC	31/12/2019	31/01/2020	1000	500	100	1000	600
Airbus A330	VP-ABC	31/01/2020	29/02/2020	1000	600	100	1000	700
Boeing B777	VQ-DEF	31/12/2019	31/01/2020	1500	450	150	1500	600
Boeing B777	VQ-DEF	31/01/2020	29/02/2020	1500	600	150	1500	750

Depreciation would be calculated as follows:

([Initial cost at start]+ [Accumulated depreciation at  start]) / DateTimeDiff([Rent ending date], [Period start], 'days') * DateTimeDiff([Period end] , [Period start], 'days')

where [Accumulated depreciation at  start] is [Accumulated depreciation at end] from previous row.

I have come up with the idea of batching an iterative macro that would process every row and calculate depreciation for each period based on [Initial cost at end] and [Accumulated depreciation at end] from previous iteration (i.e. row) but having hard time to put it into Alteryx.

Would really appreciate your help and any suggestion to solve the issue.

Thank you!

[Qiu]

@Napster 

Can you defined *Rent ending date*?

[Napster]

Good afternoon, 

The company rents aircraft for its operational purposes under lease agreements. Each aircraft has its own agreement and stipulates lease term, usually it's 12 years from the lease starting date, which is when the company physically received and accepted the aircraft. It is also contained in my dataset but i have omitted it for the purposes of hypothetical example. 

[Qiu]

@Napster 
Thank you but the *Rent ending date* is in the formula so I think we need it?

[Napster]

@Qui

You are absolutely right. See the updated table with rent starting and ending dates below:

Aircraft Type	Aircraft ID	Rent starting date	Rent ending date	Period start	Period end	Initial cost at start	Accumulated depreciation at  start	Depreciation for period	Initial cost at end	Accumulated depreciation at  end
Airbus A330	VP-ABC	31/12/2009	31/12/2021	31/12/2019	31/01/2020	14400	12000	100	14400	12100
Airbus A330	VP-ABC	31/12/2009	31/12/2021	31/01/2020	29/02/2020	14400	12100	100	14400	12200
Boeing B777	VQ-DEF	31/12/2010	31/12/2022	31/12/2019	31/01/2020	21600	16200	150	21600	16350
Boeing B777	VQ-DEF	31/12/2010	31/12/2022	31/01/2020	29/02/2020	21600	16350	150	21600	16500

[Qiu]

@Napster 
I think we have to assume that the 'Accumulated depreciation at  start' is not available and need to take ' Accumulated depreciation at end' from last row?

Made something and I dont think this is it, but need your comments before going any further.

[Napster]

@Qui

Yes, you are right. Still there are some details to mention. I have "Accumulated depreciation at start" at 31/12/2019 only, there is no data on that for the rest of the year. The logic is to calculate depreciation for January, then calculate "Accumulated depreciation at end" for 31/01/2020 as "Accumulated depreciation at start" +  "Depreciation for period" and after that take "Accumulated depreciation at end" for 31/01/2020 as "Accumulated depreciation at start" as an input for calculating depreciation for February etc. And that's how the loop should go on till the aircraft is changed, when the loop starts from the beginning