Looping calculations

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

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

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!

TomWelgemoed · Answer

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

Aircraft depreciation.yxzp

Napster · Answer

Hi @Maskell_Rascal

Please see the sample data in the attachment

Sample data.xlsx

Maskell_Rascal · Answer

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