Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

How to ignore negative values in a Pivot Table

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:

IDTBL1.Col1
110
120
1-5
210
220
25

IDTBL2.Col2
12
13
14
22
23
24

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

IDColA
16
2100

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:

IDColA
10
2100

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!

1 Solution

Accepted Solutions
Not applicable
Author

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

View solution in original post

6 Replies
Not applicable
Author

Hi

First I think you need to merge your 2 table

IDCol1Col2
1-54
1102
1203
254
2102
2203

Look at the attachement

Then just use for expression

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

JJ

Not applicable
Author

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

Anonymous
Not applicable
Author

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
Not applicable
Author

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

which will solve ur problem

Not applicable
Author

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
Not applicable
Author

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.