Trouble Replicating Excel Logic - Mutually Dependent Columns

[angusfenney]

Hi everyone,

I've been struggling with replicating parts of an excel sheet that I am trying to replace with Alteryx. I am just interested in replicating the formulas for columns K and L but I'm unsure if these are possible due to the colmuns being dependent on one another.

Any help/advice would be greatly appreciated.

Thanks,
Angus

[jrlindem]

You should be able to replicate those formulas.  I would use the Running Total Tool and the Multi-Row-Formula Tool to accomplish this.  Running Total helps you get, was the name implied the running total.  The Multi-Row-Formula Tool is going to help you compare to other fields and check against previous values.

See if this gets you in the ballpark.  If not, I can build out an illustrative workflow.  -Jay

[angusfenney]

Hi Jay, 

Thanks for reaching out, I have been experimenting with these for a while but cannot get the logic exactly right. If it's not too much trouble could you take a look at building it out with my example please?

Thanks,
Angus

[jrlindem]

@angusfenney 

Sorry, life’s been busy.  Hopefully you have already solved this!  But if not, here’s the interpretation:

In order to do this, you really needed to depart from the complicated SUMIF’s from your excel example.  They were overcomplicating things and since excel can compute row by row, but Alteryx computes column by column there was an inherent incompatibility you were running up against (and me for a while).

What I noticed though, is there was a relationship between some of the previous computed columns and I could leverage that to my advantage and avoid the “running total” aspect of your Column K and L formulas.

Now that said, you could have built an iterative macro, but depending on how many rows of data you may or may not have, that might not be practical either.

Anyway, here’s your solution (and attached).  You can see each tool labeled to match your example data.  Once I got away from the SUMIFs, it became much clearer…

Hope this is still of need, but if not, it’s at least here for others to reference.

Cheers, -Jay