Hello all, I need to sum just the negative amounts of each line. For example: ClientSum1Sum2Sum3Sum4X10-1020-5Y-300-2040Z-20-2040-20 I need to get the results that X=-15, Y=-50, Z=-60. Do you have any suggestions of how should i do it? Thank you in advance.

Sum of just negative amounts

Hello all,

I need to sum just the negative amounts of each line.

For example:

Client	Sum1	Sum2	Sum3	Sum4
X	10	-10	20	-5
Y	-30	0	-20	40
Z	-20	-20	40	-20

I need to get the results that X=-15, Y=-50, Z=-60.

Do you have any suggestions of how should i do it?

Thank you in advance.

Common Use Cases

Accepted answers

LordNeilLord

Hey @Laurami

If you transpose your data first it will be a lot easier to work with. Then you can filter on values that are less than 0. After that it's just a simple sum

@LordNeilLord

Part time Tableau, Part Time Alteryx. Full Time Awesome

Data Lover

SumNeg.yxmd

bharti_dalal

Hi @Laurami,

I am attaching the solved workflow. I hope that helps Screenshot (153).png

Totalsum.yxzp

All comments

LordNeilLord

Hey @Laurami

If you transpose your data first it will be a lot easier to work with. Then you can filter on values that are less than 0. After that it's just a simple sum

@LordNeilLord

Part time Tableau, Part Time Alteryx. Full Time Awesome

Data Lover

SumNeg.yxmd

bharti_dalal

Hi @Laurami,

I am attaching the solved workflow. I hope that helps Screenshot (153).png

Totalsum.yxzp

MarqueeCrew

@Laurami,

My friend @LordNeilLord enjoys it when I chime in on his posts with useless(ful) trivia. I hope that you mark both solutions as accepted, but here is my take on the challenge:

@bharti_dalal shows you how to compute the total sum of the negative values by row. This requires you to configure a formula using all variables to be added. This approach requires LESS resources than the solution from @LordNeilLord but it may take longer to write the code and it is less dynamic. It is however more efficient because it doesn't create new rows of data and with a large dataset it will run faster. I would however use a different multi-field formula. Instead of an IF statement (IIF), I would use a MIN() statement. Take the MIN of 0 and the field. It will run faster.

@LordNeilLord's solution is the way that I would write the code typically when there are either a large number of fields to be summed or if the fields will change from one run to the next (e.g. date columns change between runs). That being said, if you have new date columns coming into the mix each run, you might want to add a DYNAMIC select to the flow.

Cheers,

Mark

LordNeilLord

@MarqueeCrew I do enjoy it! Sometimes it makes me cry but overall it's a positive!

@LordNeilLord

Part time Tableau, Part Time Alteryx. Full Time Awesome

Data Lover

bharti_dalal

@MarqueeCrew Thanks for your feed back.I thought of using MIN() but then I went other way round;)

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15878

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703