topic Re: summing negative values in separate column in QlikView

summing negative values in separate column

CB308 — Tue, 07 Jun 2011 17:37:22 GMT

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

summing negative values in separate column

erichshiino — Tue, 07 Jun 2011 17:56:52 GMT

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

summing negative values in separate column

CB308 — Tue, 07 Jun 2011 18:35:20 GMT

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

summing negative values in separate column

erichshiino — Tue, 07 Jun 2011 18:40:25 GMT

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'

Re: summing negative values in separate column

johnw — Tue, 07 Jun 2011 23:29:10 GMT

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

summing negative values in separate column

Wed, 08 Jun 2011 07:21:00 GMT

You could try with this expression:

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

Hope help you

Re: summing negative values in separate column

CB308 — Wed, 08 Jun 2011 12:47:44 GMT

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?

Re: summing negative values in separate column

johnw — Wed, 08 Jun 2011 16:46:28 GMT

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.

Re: summing negative values in separate column

CB308 — Wed, 08 Jun 2011 17:45:10 GMT

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

Re: summing negative values in separate column

johnw — Wed, 08 Jun 2011 18:01:39 GMT

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.

Re: summing negative values in separate column

CB308 — Wed, 08 Jun 2011 18:02:52 GMT

that did help... i'll let you know the results and thank you for your time. It is appreciated.

Re: summing negative values in separate column

CB308 — Wed, 15 Jun 2011 12:42:13 GMT

thank you for all the answers, still having an issue, but using the same data, how would I calculate lets use "Ending Postion" percent to total of the "Ending Position" column?

Currently my calculation for Ending Position is column(1) + column(3), + column(5) onwards.

=num(sum(("Ending Position") ) / sum (Total ("Ending Position") is obviously not correct, need help please.