community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

Incremental Field Calculations

Highlighted
Atom

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

 

Alteryx Certified Partner

Hi,

 

 

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

 

 

Attached Workflow

Atom

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

Atom

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
Labels