How to Hide rows if two expression subtractions is zero

[sangeeth8787]

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

[Kushal_Chawda]

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)

[doris456]

Thanks for sharing this info

[sangeeth8787]

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

[Kushal_Chawda · Accepted solution]

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

[Anil_Babu_Samineni]

Try this way for 1st and 2nd measures?

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

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

[sangeeth8787]

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

[sangeeth8787]

Hi Anil,

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

[Kushal_Chawda]

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

[sangeeth8787]

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.