How do I hide rows when Null value exist?

[joe_nguyen]

What setting in the document allows me to dynamically hide rows in a table where one of the cell values = null?

Thanks,

Joe

[johnw]

If you mean a DIMENSION cell value, Properties -> Dimensions -> select the dimension -> Suppress When Value Is Null.

If you mean an EXPRESSION cell value, and there's only one expression, Properties -> Presentation -> Suppress Zero-Values.

If you mean an EXPRESSION cell value, and there is more than one expression, and you want to hide the row if ANY of the expressions are null, that's when things get complicated. But I won't try to answer that unless that's what you meant.

[joe_nguyen]

It's the third, most complicated one.

Thanks,

Joe

[johnw]

Ah, OK. Well, then let's say your table is currently this:

Dimension = Customer
Expression 1 = sum(Amount)
Expresison 2 = avg(Quantity)

This is one way to make them both disappear if either is null (untested):

Dimension = Customer
Expression 1 = if(len(sum(Amount)) and len(avg(Quantity)),sum(Amount))
Expression 2 = if(len(sum(Amount)) and len(avg(Quantity)),avg(Quantity))

And here's another way (also untested):

Dimension = aggr(if(len(sum(Amount)) and len(sum(Quantity)),Customer),Customer)
Expression 1 = sum(Amount)
Expresison 2 = avg(Quantity)

[joe_nguyen]

John, thanks for the excellent suggestion. However, I found out that one of my expressions is using Rank, so it looks like the setting to hide 0 / null is being disregarded by QV.

Any other suggestion?

[manisha10]

Hi John,

This expression is not working for me! 

A	B	Actual	Target	Difference	MaxValue
Commercial	Unsecured	20	11	9	9
Consumer	Secured	11	11	0	-
Consumer	Unsecured	5	11	-6	-
Commercial	Secured	4	11	-7	-

Dimensions: A,B,Actual, Target 

Measure:  MaxValue = if(fabs(Actual-Target)=max(Total(fabs(Actual-Target))),fabs(Actual-Target),null())

Expressions used for Dimensions is like this: 

= aggr(if(len([MaxValue]),A),A)

It is showing error.

Error: 

A	B	Actual	Target	Difference	MaxValue
-	Unsecured	20	11	9	9
-	Secured	11	11	0	-
-	Unsecured	5	11	-6	-
-	Secured	4	11	-7	-