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

Accepted Solutions
Highlighted
MVP
MVP

Another way is to create a set expression.

Let's say you want to hide the SalesID dimension for below expressions

Expr1 - Sum(Sales)

Expr2- Sum(Qty)

Your expression will look like below

Expr1:

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

or

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

Expr2:

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

or

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

Expr1-Expr2

sum(Sales)-sum(Qty)

This way, Suppress zero value option should work

View solution in original post

17 Replies
Highlighted
MVP
MVP

Suppress zero value option won't work here until you have Exp1 & Exp2, because they have values for the month. It will work if your chart only have single expression Exp1-Exp2.

But what you can do is to create calculated dimension like below and allow 'suppress when value is null' option for dimension

= aggr(if(Exp1-Exp2=0,null(),Month), Month)

 

Highlighted
Contributor
Contributor

Thanks for sharing this info

Highlighted
Contributor III
Contributor III

Hi Kush,

 

I am glad for your message, for simplicity I have used Months here, but my dimensions is something like salesId, millions of rows, I guess there will be performance issues. And also on other hand, I have 4 dimensions in my pivot chart. 

 

 

Thanks

 

Highlighted
MVP
MVP

Another way is to create a set expression.

Let's say you want to hide the SalesID dimension for below expressions

Expr1 - Sum(Sales)

Expr2- Sum(Qty)

Your expression will look like below

Expr1:

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

or

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

Expr2:

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

or

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

Expr1-Expr2

sum(Sales)-sum(Qty)

This way, Suppress zero value option should work

View solution in original post

Highlighted

Try this way for 1st and 2nd measures?

Expression 1 : Sum({<Month={"=Sum(Exp1)>0"}>} Exp1)

Expression 2 : Sum({<Month={"=Sum(Exp2)>0"}>} Exp2)

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Highlighted
Contributor III
Contributor III

Hi Kush, 

 

I have tried by taking all other dimension other than salesId and used these expressions but still not able to hide rows.

Thanks

 

Highlighted
Contributor III
Contributor III

Hi Anil,

 

I am glad for your message, but it didn't work for me.

 

 

Highlighted
MVP
MVP

Would you be able to share the screenshot of expressions used and what you are getting in chart?

Highlighted
Contributor III
Contributor III

Hi

 

Exp1:

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

--=SUM(NET_INVOICE_AMT)

 

Exp2:

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

--=sum((TOTALTICKETSVALUE))

 

Exp2-Exp1.