Incremental Field Calculations

[RFender]

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

[geraldo]

Hi,

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

Attached Workflow

[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

[RFender · Accepted solution]

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