How to calculate formulas with a left to right priority first and then from top to bottom?

[AkisM]

My title is a little vague so I'll try to explain by including some sample data and formulas.

The only columns I'm having trouble translating to Alteryx are the ones I highlighted as green. (S, AC, AE).

You'll notice that column S references column AE in the formula. So I thought ok, I have to have a tool that calculates AE first.

But then AE references AC. So I figured ok, then the right order is a multi-row formula for AC, then another for AE, then another for S. 

But then again, even AC, references back to S-1 (previous row). So it's somewhat of a circular formula reference that I'm not sure how to translate in alteryx.

Any assistance would be appreciated. You can use the sample data I provide as the input file, as long as you first zero out the  highlighted columns so that the calculation is done from scratch by alteryx.

[ChrisTX]

See if the attached workflow helps.  You may need to change the new field data types from Fixed Decimal to Double.

Chris

[AkisM]

Hi @ChrisTX , thanks for your response but unfortunately that doesn't help because in the first formula you used, you're referencing the field S (average cost per unit), which for the purposes of this problem is supposed to be 0 (not calculated yet). That's why I said make sure you zero out all highlighted columns before using the sample data as input. The data in the highlighted columns is just to explain the formula. In the real problem that data does not exist (it's 0 and needs to be calculated).

[ChrisTX]

Try the multi-row, multi-field macro from @patrick_digan in this post:

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Applying-multi-row-formula-to-multiple...

[AkisM]

Unfortunately I wasn't able to come to my desired solution with that tool either. It's a bit too confusing to use. I'm sure this is doable with the stock Alteryx tools as well, just need to figure out how to perform the calculations in the correct order. It looks very simple in the excel formulas, not sure why it's not that simple specify the order of calculations in alteryx.

[ChrisTX]

@AkisM see if the attached workflow helps.  It uses an iterative macro.

A few of the amounts it calculates are different from your sample data.  Should any of the amounts be reset to zero when an Opening Balance record is found?

Chris

[AkisM]

Hi @ChrisTX ,

Sorry for the reply and thanks for the time and effort you put into this. To answer your question, AC column is always zero if description = "purchase". But the rest of your results look correct which is great. Unfortunately however it looks like I'm unable to open your workflow at all since we're using different versions (my version is 2019.3.5.17947)

[ChrisTX · Accepted solution]

See attached updated workflow.  I changed the field names to make it easier to understand.  Basically, you use 3 Multi-Row Formula tools inside an Iterative macro.  The iteration allows you to loop, and retain access to data in previous rows.  

Without a loop, a Multi-Row Formula tool will calculate values down an entire column first.

To adjust the Alteryx version number inside a workflow, so you can open it with an older version:  see attached file Adjusting Alteryx Files for Different Versions.docx

Chris