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

Nested IF while, ignore negative values with exception

Hello everyone,

I have a special case/user requirement that in a pivot table I would show a SUM, ignore negative values from the source table BUT make one exception.

I believe a nested IF should do the trick, but all my tries so far failed; I got a bunch of different results... except of course, the one I'm looking for :-).

It looks like my syntax is correct, but my logic is wrong; I was wondering if the Community may be able to point me in the right direction.

SOURCE TABLE 1

IDValue1
110
220
31

SOURCE TABLE 2

IDValue 2
110
2-10
3-20

My Pivot Table is simple:

Dimmension: ID

Expression: Sum(Value1 * Value2)

The desired Pivot Table would look like this:

Header 1Header 2
1100
20
3-20

Basically:

1. IF(Sum(Value1*Value2)<=0,0,Sum(Value1*Value2))

2. IF(Table2.ID = '3', Sum(Value1*Value2))

How do I combine the two IFs so that it will ignore (0) all negative values in SOURCE TABLE 2 except where ID=3?

Thank you very much in advance

1 Solution

Accepted Solutions
Not applicable
Author

I think that this expression will help...

IF(ID = 3, Sum(Value1*[Value 2] ),

IF(Sum(Value1*[Value 2])<=0,0,Sum(Value1*[Value 2]))

)

See attached file for the sample.qvw that I made for this issue

View solution in original post

7 Replies
Not applicable
Author

I think that this expression will help...

IF(ID = 3, Sum(Value1*[Value 2] ),

IF(Sum(Value1*[Value 2])<=0,0,Sum(Value1*[Value 2]))

)

See attached file for the sample.qvw that I made for this issue

Anonymous
Not applicable
Author

Thank you very much Roberto for your quick reply; I have tried your expression and it works to some degree.

What I mean by that is the numbers in the Combined column are OK, but when I do a Total for the ID dimenssion, the result is 0, when it should be 80.

QV_negValuesIssue.jpg

Either I'm missing something, or this is kinda strange behaviour from QV.

Not applicable
Author

You may want to try moving your sum aggregation outside of the IF statement. 

COMBINED Column

sum(IF(ID = 3, Value1*[Value 2],

if(Value1*[Value 2]<=0

  ,0

  ,Value1*[Value 2]

  )

)

)

I've attached Roberto's sample with the modified formulas.

jemancilla
Contributor III
Contributor III

If you want that it ignores only the negative values in TABLE 2, except when ID=3, then .......

Sum(If(ID=3,Value2*Value1,If(Value2<=0,0,Value2*Value1)))
Not applicable
Author

Ah, I didn't notice the incorrect total. Datawitch indeed seems to have the corrected version for you 🙂

Anonymous
Not applicable
Author

Absolutely great stuff; Datawitch expression does give me the result I want. I struggled with this for a few days and now I look at the solution and it seems so simple! Not a good feeling for me :-).

Thank you very much everyone for the help!

PS Apologies to Datawitch as I can only have one correct answer.

Not applicable
Author

No worries, I'm glad I was able to help.  I ran into that a few months ago myself.