summing negative values in separate column

[CB308]

Hello

How can I realize this logic/formula?

If I have a total of two separate columns and the value is negative (see Position-stock), how do I show only the negative values in a separate column and able to total all negatives?  Currently my total is zero instead of  5493405.

Thank you in advance!!!!

Totals	Ending Position	Stock	Position-Stock	Negative Totals as Positive
Totals	25453598	23003823	2449775	0
PLine1	19591501	20736596	-1145095	1145095
Pline2	8995999	13344309	-4348310	4348310

[erichshiino]

What field are you using? it may also depend on your expression.

But perhaps, a set analysis expression can do the trick:

sum ( {<VALUE = {'<0'}>} VALUE)

Hope this helps,

Erich

[CB308]

i'm using column totals instead of the fields as the fields were calculated totals of many different fields.. make sense?

[erichshiino]

Sorry, I don`t think I understood.. What is the expression for: 'Negative Totals as Positive'?

If it is a straight table, you can go to the expressions tab, select the expression for 'Negative Totals as ....'

and then, on the right side, change the total mode to 'Sum of Rows'

[johnw]

So "Postition-Stock" is your label for the column, which is a complicated expression of some sort?  You can just refer to the column label:

if("Postition-Stock"<0,-"Position-Stock")

For the column total, just set it to sum of rows as Erich says

[#6]

You could try with this expression:

if("Position-Stock">=0,"Position-Stock"-"Position-Stock","Position-Stock"*-1)

Hope help you

[CB308]

The answers have been very helpful however since I am using a pivot table, I'm looking in my example to show the value of 5493405 instead of zero for the total of the column in the example above...

any ideas?

[johnw]

I haven't tried this, but it might work:

sum(aggr(if("Postition-Stock"<0,-"Position-Stock"),Your,Dimensions,Here))

If not you can just repeat the expression for position stock, and it should work.  If you consider the expression too long to repeat, make a variable for it.

[CB308]

can you dumb it down for me?  where you wrote your, dimensions, here... ?

for "Position-Stock"  it is just column (50) - column (51)  Ending Position - Stock.... 

[johnw]

I don't know what dimensions you're using in your chart, so I'm not sure how much more I can dumb it down for you.  If your dimensions are "Customer" and "Location" then you'd use those dimensions where I put "Your,Dimensions,Here".  If you really need me to write the whole expression exactly as it should appear and verify that it works for you, you're going to have to post your application.  Otherwise you may need to think about how to apply what I'm saying.  For instance, you could notice that I'm using the aggr() function where I wrote "Your,Dimensions,Here" and look up the aggr() function in the help text to see what parameters it takes.  If you're still confused about what to put there, I recommend doing exactly that.

Why I think it might not work is that I'm not confident aggr() will aggregate column names or positions, and may require the underlying expression to aggregate.  I could expand on my answer for what to do in that case, but if the first approach works, I'd rather stick with that.