# QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join us for a live Q&A! September 21, 10 AM ET - Onboarding Fast in Qlik Sense SaaS! REGISTER
cancel
Showing results for
Search instead for
Did you mean:
Contributor III

## summing negative values in separate column

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
11 Replies
Partner

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

Contributor III
Author

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

Partner

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'

Champion III

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

Not applicable

You could try with this expression:

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

Hope help you

Contributor III
Author

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?

Champion III

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.

Contributor III
Author

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

Champion III

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.

Community Browser