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]

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

[Kushal_Chawda]

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

[sangeeth8787]

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

[Kushal_Chawda]

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

[Kushal_Chawda]

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)

[sangeeth8787]

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

[Kushal_Chawda]

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

[sangeeth8787]

This expression worked with small amendments

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