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

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.

Sample Input.xlsx

Workflow

Accepted answers

ChrisTX

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

Example 1c Calculate across rows first - Workflow.yxzp

Adjusting Alteryx Files for Different Versions.docx

All comments

ChrisTX

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

Chris

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

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

@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

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

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

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

Example 1c Calculate across rows first - Workflow.yxzp

Adjusting Alteryx Files for Different Versions.docx

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15875

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703