Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

adrianad
New Contributor III

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

Re: Nested IF while, ignore negative values with exception

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

7 Replies
Not applicable

Re: Nested IF while, ignore negative values with exception

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

adrianad
New Contributor III

Re: Nested IF while, ignore negative values with exception

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

Re: Nested IF while, ignore negative values with exception

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
New Contributor III

Re: Nested IF while, ignore negative values with exception

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

Re: Nested IF while, ignore negative values with exception

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

adrianad
New Contributor III

Re: Nested IF while, ignore negative values with exception

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

Re: Nested IF while, ignore negative values with exception

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

Community Browser