topic Re: Null values not showing in bar chart in QlikView

Null values not showing in bar chart

Wed, 05 Mar 2014 11:33:06 GMT

I have an expression, which work great, as the bar chart shows all values including null ones in my stated dimension,which is called TRADE_CONDITION_1

The expression is

COUNT({$<Book={'Supranationals - GSL'}>}[Trade Tag])

However, when I add in an an extra set analysis, so as to not show some of the values of the TRADE_CONDITION_1 dimension, the null column disappears!

COUNT({$<TRADE_CONDITION_1-={'IF','ICO','FLT','INT','AD','GP','RN','NCR','BRR','AVP'},Book={'Supranationals - GSL'}>}[Trade Tag])

I have not changed any other settings. 'Supress when value is null' is NOT ticked, nor is 'Supress zero-values' or 'Supress Missing'. And i can't tick 'show all values' because i don't want to see all values.

Re: Null values not showing in bar chart

jonathandienst — Wed, 05 Mar 2014 12:10:05 GMT

Remember that -= is a shortcut, so for example

{<DIM1 -={'Something'}>} is shorthand for {<DIM1 ={'*'} - {'Something'}>}

So this expression selects all NON-null values and then removes the 'Something' entries.So the nulls are excluded before the -= condition is evaluated.

HTH

Jonathan

Re: Null values not showing in bar chart

jonathandienst — Wed, 05 Mar 2014 12:14:49 GMT

You could include the nulls by adding this to the end of your expression:

+ COUNT({<Book={'Supranationals - GSL'}>} If(IsNull(TRADE_CONDITION_1), [Trade Tag]))

Or you could use NULLASVALUE TRADE_CONDITION_1; in your script to make TRADE_CONDITION_1 selectable.The nulls will be replaced by empty strings which will be selected by the 'all' ('*') selection.

HTH

Jonathan

Re: Null values not showing in bar chart

Wed, 05 Mar 2014 12:16:10 GMT

Aha. I didn't know that! That explains a lot

So how would i exclude the values i want to, without excluding the null values?

Re: Null values not showing in bar chart

jonathandienst — Wed, 05 Mar 2014 12:18:56 GMT

Perhaps you missed my second post (posted just before your reply) which answers your question.

Re: Null values not showing in bar chart

Wed, 05 Mar 2014 12:27:41 GMT

This is giving me some funny results, but i think it's almost what i need.

It may actually be easier for me to include the TRADE_CONDITION_1s I want instead of the ones I don't want. but how would I get null values included. Would this work?

COUNT({$<TRADE_CONDITION_1={'VC','NRR','EL',NULL},Book={'Supranationals - GSL'}>}[Trade Tag])

Re: Null values not showing in bar chart

jonathandienst — Wed, 05 Mar 2014 12:40:46 GMT

I would try this:

COUNT({<Book={'Supranationals - GSL'}>} If(Match(TRADE_CONDITION_1, 'VC','NRR','EL') Or Len(TRADE_CONDITION_1) = 0, [Trade Tag]))

Edit: fixed condition >0 to =0

Re: Null values not showing in bar chart

Wed, 05 Mar 2014 12:51:15 GMT

Yes, this works in conjunction with ticking 'Suppress zero values'!

thanks very much