Alteryx Designer Discussions

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

Sum rows based on the values in other columns

alteryxintern
5 - Atom

Input

 

Co-MSFabcde
0001 MSF296 5   
0001 MSF255  7  
0001 MSF296 10   
0001 MSF26511    
0001 MSF296    12
0001 MSF255   9 

 

Required output:

 

Co-MSFabcde
0001 MSF296 15  12
0001 MSF255  79 
0001 MSF26511    
3 REPLIES 3
JosephSerpis
16 - Nebula
16 - Nebula

Hi @alteryxintern you can do this multiple way the simplest is using a summarise tool to group by Co_MSF and sum up all of your columns. However this is not dynamic as you would need to specify each column you want to sum. I've come up with two approach that should guide you.

 

Sum_25112021.JPG

alteryxintern
5 - Atom

Thanks!  JosephSerpis

One more question - if I wanted to add a column at the end to sum (a+b+c+d+e) for each Co-MSF, how can I do that?

I used the formula tool to 'add a new column' and put in the formula 'a+b+c+d+e' however, it is not working, all values come as null un the new column.

JosephSerpis
16 - Nebula
16 - Nebula

Hi @alteryxintern as you mention the value are resulting in null because you are adding nulls in your caculation. You can deal with this in a couple of ways use a data cleanse tool before the formula tool to replace the nulls with 0. You can use a Multi-Field Formula tool to achieve the same affect with an If formula testing if the value is null and replace with 0. Or if you are using a crosstab tool such as I did in option B you can scroll down the method for Aggregating values in the configuration window of the cross tab tool and select total column which will provide that sum column for you.

Labels