How to ignore negative values in a Pivot Table

[Anonymous]

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!

[#2]

Hi

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

JJ

[#3 · Accepted solution]

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

[Anonymous]

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.

[Anonymous]

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

which will solve ur problem

[#6]

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

[Anonymous]

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.