How to filter out zero rows within an expression field (in Qlikview)?

[joeluqlik]

Row	Report 1	Report 2	Difference (an expression of Report 1 minus Report 2)
1	5000	6000	-1000
2	400	400	0
3	2000	1800	200
4	500	500	0

I tried to filter out rows (#2, #4 in this case) where the value is zero within the Difference column which is an expression of comparing the two reports (Report 1 and Report 2).  But can't seem to select exclude the zeros when the field is not a dimension but and expression.  Can't use Supress Zero function to do that either.

The actual data file has many zero lines and I just want to pick up the lines that are with differences in a single export to Excel.

Thanks anyone for helping with this!

JL

[sunny_talwar]

May be try this

Dimension

Row

Expression

If(Report1Expression - Report2Expression <> 0, Report1Expression)

If(Report1Expression - Report2Expression <> 0, Report2Expression)

Report1Expression - Report2Expression

The reason we have to use if statement here is because we have to force all expressions to be 0 or null for the rows where difference is null so that those rows get removed from the chart

[joeluqlik]

Hi Sunny,

Thanks for the quick response and guide here.

Are we saying it is a nested IF statement or simple a single statement like this will work?

If(R1_Amt - R2_Amt <> 0, R1_Amt - R2_Amt)

The R1_Amt is just an amount from one file, same as the R2_Amt is another amount from another file.

I am just trying to display only the non-zero difference lines in a straight table.

JL

[sunny_talwar]

I think you would need three expressions with one dimension

If(R1_Amt - R2_Amt <> 0, R1_Amt )

If(R1_Amt - R2_Amt <> 0, R2_Amt)

If(R1_Amt - R2_Amt <> 0, R1_Amt - R2_Amt)

[greend21]

Maybe it is not reading the result as a number for some reason. You could try wrapping the expression in Num(). You made sure to click your expression on the presentation tab and select Suppress Zero-Values for THAT field?

[joeluqlik]

There is syntax error.  Missing any comma or ()?

[joeluqlik]

How do you create three expressions within one dimension?

Also the zero rows that I try to remove from my straight table are within the expression column, not within a dimension.

[sunny_talwar]

Would you be able to share your application?