Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
Contributor III

How to Hide rows if two expression subtractions is zero

Hi,

 

I have three expressions on my chart

 

Month             Exp1            Exp2            Exp2-Exp1

Jan                    10                10                   0

Feb                     25               35                  10

March               45               45                       0

 

I want to see only Feb Month Data, Is it possible to hide the other two rows based on the expressions result.

 

 

Thanks

 

 

Labels (1)
17 Replies
Highlighted
MVP
MVP

try this

Expr1

=if(sum(NET_INVOICE_AMT)-sum(TOTALTICKETSVALUE)=0,0,sum(NET_INVOICE_AMT))

Expr2

=if(sum(NET_INVOICE_AMT)-sum(TOTALTICKETSVALUE)=0,0,sum(TOTALTICKETSVALUE))

Highlighted
MVP
MVP

or just this

Exp1:

Sum({<SALES_INVOICE_ID={"=Sum(NET_INVOICE_AMT)-Sum(TOTALTICKETSVALUE)"}>} NET_INVOICE_AMT)

--=SUM(NET_INVOICE_AMT)

 

Exp2:

Sum({<SALES_INVOICE_ID={"=Sum(NET_INVOICE_AMT)-Sum(TOTALTICKETSVALUE)"}>} TOTALTICKETSVALUE)

--=sum((TOTALTICKETSVALUE))

 

Highlighted
Contributor III
Contributor III

Hi kush,

The Set Expression one worked for me, after changing to 

Sum({<SALES_INVOICE_ID={"=Sum(NET_INVOICE_AMT)-Sum(TOTALTICKETSVALUE)>0.1"}>} NET_INVOICE_AMT).

 

I am not sure why I am seeing this behaviour, but the difference between two columns output sometimes showing as 0.0000048 though I have round to two digits in SQL Query. Do you got any clues about it?

 

Thanks

Highlighted
MVP
MVP

No need to specify >0.1, I think set expression suggested above should work

Highlighted
MVP
MVP

Also, this set expression won't work properly as Subtraction  may give positive or negative values, So >0.1 will exclude the actual value from both the expression. Try like below

Expr1-

Sum({<ID={"=sign(sum(Invoice)-Sum(Ticket))=1 or sign(sum(Invoice)-Sum(Ticket))=-1"}>}Invoice)

Expr2-

Sum({<ID={"=sign(sum(Invoice)-Sum(Ticket))=1 or sign(sum(Invoice)-Sum(Ticket))=-1"}>}Ticket)

Highlighted
Contributor III
Contributor III

May I know what is the use of 1 or -1 here?

Highlighted
MVP
MVP

sign function just checks whether value is positive(1) or negative(-1). After subtract we may get result as positive or negative which we may need to tie it with original values

Highlighted
Contributor III
Contributor III

This expression worked with small amendments

 

Sum({<SalesID ={"=fabs(sum(Sales)-sum(Qty))>0.1"}>}Sales)