Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

Sum across multiple Columns on a single row

HBromell
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 

5 REPLIES 5
dataMack
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])

HBromell
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

HBromell
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.   

danielbrun2
ACE Emeritus
ACE Emeritus

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

truct-kp
6 - Meteoroid

This works well. 

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

 

Labels