Batching iterative macro

Question

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 TypeAircraft IDPeriod startPeriod endInitial cost at startAccumulated depreciation at  startDepreciation for periodInitial cost at endAccumulated depreciation at  endAirbus A330VP-ABC31/12/201931/01/202010005001001000600Airbus A330VP-ABC31/01/202029/02/202010006001001000700Boeing B777VQ-DEF31/12/201931/01/202015004501501500600Boeing B777VQ-DEF31/01/202029/02/202015006001501500750

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!

Napster · Answer

@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

Qiu · Answer

@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.

1224-Napster.yxmd

Napster · Answer

@Qui

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

Aircraft TypeAircraft IDRent starting dateRent ending datePeriod startPeriod endInitial cost at startAccumulated depreciation at  startDepreciation for periodInitial cost at endAccumulated depreciation at  endAirbus A330VP-ABC31/12/200931/12/202131/12/201931/01/202014400120001001440012100Airbus A330VP-ABC31/12/200931/12/202131/01/202029/02/202014400121001001440012200Boeing B777VQ-DEF31/12/201031/12/202231/12/201931/01/202021600162001502160016350Boeing B777VQ-DEF31/12/201031/12/202231/01/202029/02/202021600163501502160016500