Qlik Community

Ask a Question

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: 
CB308
Contributor III
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
erich_shiino
Partner
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

CB308
Contributor III
Contributor III
Author

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

erich_shiino
Partner
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'

johnw
Champion III
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

CB308
Contributor III
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?

johnw
Champion III
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.

CB308
Contributor III
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.... 

johnw
Champion III
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.