topic Re: How to ignore negative values in a Pivot Table in QlikView

How to ignore negative values in a Pivot Table

Anonymous — Wed, 30 Nov 2011 20:02:00 GMT

Hello; I am new to the QlickView application and I am facing an issue that should be pretty simple to resolve, however, so far I was unable to.

Here is a simple example of what I would like to accomplish; I have 2 tables with the following data:

ID	TBL1.Col1
1	10
1	20
1	-5
2	10
2	20
2	5

ID	TBL2.Col2
1	2
1	3
1	4
2	2
2	3
2	4

I have a Pivot Table with ID as Dimension and with expressions: Sum(TBL1.Col1*TBL2.Col2). My Pivot table show like this:

ID	ColA
1	6
2	100

While this is mathematically correct of course, my particular need is to ignore the negative value while processing the rest of the rows so that my result would be 80 in case of ID1.

I have tried the following, but it does not give me the result I want: If (Sum(Col1*Col2)>0, Sum(Col1*Col2), 0)

For all the negative value I would get a 0, whereas if I have no negative values I would get the correct result as seen below:

ID	ColA
1	0
2	100

Also, I would like to avoid using an IF condition in the SQL statement, as those numbers may be included in another straight Table.

Any suggestions would be extremely appreciated; thank you in advance!

Re: How to ignore negative values in a Pivot Table

Wed, 30 Nov 2011 21:04:17 GMT

First I think you need to merge your 2 table

ID	Col1	Col2
1	-5	4
1	10	2
1	20	3
2	5	4
2	10	2
2	20	3

Look at the attachement

Then just use for expression

=sum( if( Col1*Col2 > 0 , Col1 * Col2 , 0))

How to ignore negative values in a Pivot Table

Wed, 30 Nov 2011 21:48:20 GMT

You could try sum(if(col1*col2<0,0,col1*col2))

How to ignore negative values in a Pivot Table

Anonymous — Thu, 01 Dec 2011 02:57:48 GMT

I had 3 cases out of 11 where I had negative values that needed to be ignored; I've used your expression... and it works in 2 cases.

I'll have to look at the data for the remaining case that still gives me problems, as I feel this may be related to the data.

PS JJ, thank you for the reply; I will try your advice as well.

How to ignore negative values in a Pivot Table

Anonymous — Thu, 01 Dec 2011 06:47:28 GMT

u can also use rangemax(Sum(TBL1.Col1*TBL2.Col2),0)

which will solve ur problem

Re: How to ignore negative values in a Pivot Table

Thu, 01 Dec 2011 14:07:58 GMT

if their are negatives in both col1 and col2 then you might want to qualify that in your if statement instead. Since multiplying them together would give you a positive number.

sum(if(col1<0 or col2<0,0,col1*col2))

How to ignore negative values in a Pivot Table

Anonymous — Thu, 01 Dec 2011 16:53:42 GMT

Got it working; the last piece was a problem with the data. Once that was rectified... it works perfectly.

This is my first interaction with this fine community, and I have to say it, I am very impressed with the knowledge and the willingness to help!

Thank you very much everyone; much appreciated.