Filtering by value to also include null

[justin_morley]

Hi all, I have a hypothetical dataset like this:

Col1	Col2	Col3
10	A	-
20	B	-
30	-	X
40	C	-
50	D	-

I always want to see the value in Col3 regardless of selection state.

So If I select "B" from Col2 anywhere in my app, I want my straight table to look like this:

Col1	Col2	Col3
20	B	-
30	-	X

If Col3 is not null, Col2 is always null and vice-versa.

Could I employ field triggers to acheive this?

[alexandros17]

in presentation tab (properties of your table) uncheck the box "suppress zero values"

[justin_morley]

Hi Alessandro,

Thanks for your reply, but unfortunately this doesn't work.

Filtering by "B" say leaves you with a single row rather than the two rows I require

Justin

[alexandros17]

could you post your document?

[Gysbert_Wassenaar · Accepted solution]

You can use a straight chart with the three fields as dimensions and an expression like sum({$+<Col1,Col2,Col3={'X'}>}1). On the presentation tab you can hide the expression column. See attached qvw. If you want to always see any non-null value in Col3 then use '*' instead of 'X'.

[justin_morley]

Hi Gysbert,

Spot on. Thanks very much for the answer.

Now to get my head around your set expression!

Justin