Dynamic selection of columns for calculations

Hello everyone!

I am trying to find a solution for the following problem in Alteryx:

I have a table (see attached screenshot) with the columns: Name, 950020, 950021, 950010, Delta.

Depending on my input data it might be that additional columns with the same type of data as in 950020, 950021, 950010 may appear. So the table might for example contain the columns: Name, 950020, 950021, 950010, 950011, 950012, Delta.

My goal is to create an additional column ("Calculation") which sums up the values of the number clumns (950020, 950021, 950010 ...) and substract their sum from the value in column Delta.

---> = (950020+ 950021+ 950010...) - Delta

Since the number of the column type 950020, 950021, 950010... is subject to change I am trying to find a dynamic solution, meaning that all number columns present in the table are considered in my calculation. Therefore, using the formula tool and referring to the columns (classic calculation) is no option to me.

Does anyone have an idea of how to solve this problem?

I appreciate any help that you can offer! Thank you!

Best regards, Kim

image.png

Workflow

Accepted answers

jrlindem

Here's an example of how you can solve this. I used your example image and put in some fake values to illustrate. You don't want to use "Name" as a field since the Transpose will create a Name and Value field. I mean... you can, but it's annoying to have to rename, etc.

And here's the solution:

I've also attached the workflow for you to reference or adapt from. Hope this helps, -Jay

jrlindem_example.yxmd

All comments

jrlindem

This is very similar to a post I helped solve for another user:

Formula and logic question. - Alteryx Community

What you need to consider is using Transpose to flip the data vertically. Then you can filter out values you don't need to apply the math to, use the Summarize Tool, and then compute or flip things back to horizontal using Crosstab.

Take a look at that link and in the meantime, I'll see if I can mock something up for you. -Jay

jrlindem

And here's the solution:

I've also attached the workflow for you to reference or adapt from. Hope this helps, -Jay

jrlindem_example.yxmd

kim09

Thank you very much for your help, I successfully implemented your solution! Best regards, Kim

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15875

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703