Sum of just negative amounts

[Laurami]

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.

[LordNeilLord · Accepted solution]

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

[bharti_dalal · Accepted solution]

Hi @Laurami,

I am attaching the solved workflow. I hope that helps

[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

[bharti_dalal]

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