Alteryx Designer

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

Sum across multiple Columns on a single row

Highlighted
5 - Atom

I am trying to sum across a few columns then subtract that value from the sum of another group of collumns.  For example- I want to sum the total of all fruit, sum all vegetables then get the difference. How would I do this in alteryx? 

 

 

Baskets       Apples      Oranges    Grapes     Zuchini   Spinach    

Combo 1     10.2             2.5             31            12           

Combo 2      null             10.5          null            45.5        12.2               

Combo 3      5                  7.5           12             null          31

Combo 4      7                 12.5            7              2.5          10 

Highlighted
12 - Quasar
12 - Quasar

If it's just thoe few columns, I would do it directly like this:  Drag a Formula tool onto the canvas connected to your data.  Pick some numeric data type (ex. int32) and give it a meaningful name, for example 'FruitLessVeggies'.  Your fomrula would be:

([apples]+[oranges]+[grapes])-([zuchini]+[spinach])

Highlighted
5 - Atom

I might have found a solution!  But would like some validation.  I used the multi Row formula to sum.  The data is looking correct.. But to complete the action I wanted I had to use it three times.. Is there a faster way? 

 

MultiRow Sum.png

Highlighted
5 - Atom

Thank you!  In truth its more complicated than that, but that is the general idea.  I think some of my values downstream had an incorrect (non numeric) data type.. I have been banging my head for why the formula wasnt working.  But im guessing if I fix that it will be good.   

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

Hi,

 

I would use transpose to have values in rows instead of columns. Then I would split the datastrems (in this case using a join tool). And finnaly summarice each stream and subtract them.

 

See attached example.

 

Best,

Daniel

Highlighted
6 - Meteoroid

This works well. 

I wish alteryx just create a simple column (numeric) sum function within the formula tool. 

 

Labels