Looping calculations

[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 that I expect to get as an outcome

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

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.

There are also 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

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

Thank you!

[Maskell_Rascal]

Hi @Napster 

Could you provide some sample data prior to calculations and sample data with the expected results. If you have it calculated in an excel file with the dynamic reference formulas you mentioned, that would work as well. 

Thanks!

Phil 

[Napster]

Hi @Maskell_Rascal 

Please see the sample data in the attachment

[TomWelgemoed]

Hi @Napster ,

As I had some difficulty with marrying the dataset provided with the example you gave (didn't see period details in the sample set), I've made a simple example to show you how I would approach the problem.

Simply put, I don't believe you need an iterative macro - and you should avoid a macro if there is a simpler way. Did you consider a multi-row formula? A multi-row formula uses the results from the previous row and then computes based on that result - kind of like Excel. 

In the attached example and image below, I simplified the numbers for the sake of the example. But simply put, assuming you have the periods listed in your data set, all you need to do is to group by the Aircraft ID and then to apply a multi-row formula that looks up the previous row for the calculated result like I did below. I don't know if you know what the "standard depreciation" is, but this can perhaps be calculated like you did with the periods.

Anyway, feel free to ask clarifying questions - hopefully this get's you started.

Regards,

Tom