Incremental Field Calculations

Hello,

I'm trying to create an automated workflow where I have a number of fields (w, x, y, z & C) for which I want to perform the below calculations on to return values for wS, xS, yS & zS:

wS = w * C

xS = x * (C + wS)

yS = y * (C + wS + xS)

zS = z * (C + wS + xS + yS)

As you can see, I am trying to use a running total of the fields I've just calculated to calculate a new field.

Normally I would write out an individual formula (as above) using the formula tool, however, the number of fields / variables (w, x, y, z) is variable itself! I.e. we could have (a, b, c, …, x, y, z + more!)

Is there any way to dynamically calculate these fields?

This can be easily done in Excel (as attached) but I am creating a large automated workflow in Alteryx for which this is just one tiny part.

I've also attached an example Alteryx flow to kick things off with my current (flawed) logic - using the CReW Dynamic Formula tool.

Any help is very much appreciated!

Thanks

Ryan

ExampleIncrementalCalcs.yxmd

ExampleIncrementalCalcs.xlsx

Tips and Tricks

Macros

Best Practices

Dynamic Processing

Accepted answers

RFender

Just to close this off, I found a solution to this problem!

I don't know why I didn't think of this earlier, but I used the Python tool to do the calculation - this possibly isn't the most efficient way but I couldn't figure out how to do it using core Alteryx tools.

Python code below for those interested (apologies for the lack of formatting, I would upload my Alteryx workflow but it contains sensitive data):

constant = [10,10,10, 10]
data = [
    [0.5, 0.5, 0.5],
    [0.25, 0, 0.25],
    [0, 0.1, 0],
    [0.2, 0, 0.2],
]

print(constant)
print(pd.DataFrame(data))
print(pd.DataFrame(constant))

def sum(a,b):
return [x + y for x, y in zip(a, b)]

def multiply(a,b):
return [x * y for x, y in zip(a, b)]

def calc(data, constant):
    results = []

    prev = [0 for x in range(len(data))]

    for col in data:
        value = multiply(col, sum(prev, constant))
        prev = sum(prev, value)
        results.append(value)

return results

answer = calc(data, constant)
print(answer)

Hope this helps!

Ryan

All comments

geraldo

Hi,

Here's an example using the CreW addTotals tool. It may be for you to check usage.

Attached Workflow

calc.yxmd

RFender

Hi Geraldo,

Thank you for your speedy response, and apologies for the delay in getting back to you - I had some technical difficulties which meant I couldn't see your example until about an hour ago!

Although this is a very useful tool, my issue is that the number of variables (e.g. w, x, y & z) changes, so I may have more (or less) variables for which I want to calculate results for (e.g. wS, xS, yS & zS).

Is there any way to loop this process, changing the fields selected by the addTotals & formula tool on each iteration (based off an input list containing these variables)?

I've been playing around with macros to try and achieve this, but I seem to be going round in circles (no pun intended) & I think this is a bit above my ability!

Thanks

Ryan

Quick Links

This months top contributors

AkimasaKajitani 387

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335