BAR CHART, exclude variables in the DIMENSION

Wed, 01 Jul 2015 17:25:02 GMT

When I make a BAR CHART, I want to include the value "NULL"?

As you can see the value "NONE" and "NULL" dominates the chart, this should be eliminated, how do I do that?

Re: BAR CHART, exclude variables in the DIMENSION

Anonymous — Wed, 01 Jul 2015 18:30:23 GMT

you can do it in calculated Dimension or in Expression

Dimension:

If (Dim<>'NONE' or Dim<>'NULL',Dim,null())

Expression

sum({<Dim-={'NONE','NULL'}>}Measure

Re: BAR CHART, exclude variables in the DIMENSION

Thu, 02 Jul 2015 02:53:54 GMT

Hi Jane

As noted above you can use a calculated dimension or an expression to achieve this. A key point not mentioned above is that if you use a calculated dimension you must remember to tick the 'Suppress When Value Is Null' option.

To illustrate, you start with:

If you add a calculated dimension =If(Dimension<>'NONE' and Dimension<>'NULL',Dimension,) but forget to tick the 'Suppress When Value Is Null' option you will get:

You can see that the excluded values are now shown as a new untitled bar. Selecting the calculated dimension and ticking the 'Suppress When Value Is Null' option removes this bar so you end up with:

The option can be found on the Dimensions tab of the chart properties

In the calculated dimension formula it is important to use the correct logical operator. In the examples given in the previous answer the operator 'OR' is used. This will not work as effectively the expression is saying if the dimension is not NONE or the dimension is not NULL then display the dimension. However when it is NULL it is not NONE and when it is NONE it is not NULL so the values will persist. To get them to disappear you have to basically say only show the values where the dimension is not NULL and NONE, hence the 'and' operator will work. E.g.

=If(Dimension<>'NONE' and Dimension<>'NULL',Dimension,)

Another way to write this which may be easier to follow is =If(Dimension='NONE' or Dimension='NULL',Null(),Dimension)

Note in this version the operator 'OR' is used rather than 'AND'. What makes the difference is that in the first example the expression is based on NONE and NULL not being present whereas in the second version the expression is based on where they are present.

So to summarise the second formula above basically says if the value of the dimension is NONE or NULL then replace the value with a nulled result otherwise show the value of Dimension. With the values of NONE and NULL being converted to a nulled result then when the 'Suppress When Value Is Null' option is ticked the values NONE and NULL vanish from the chart.

To use the expression version then just use the Dimension and an expression of

sum({<Dimension-={'NONE','NULL'}>}Value1)

which is basically saying 'only sum the values in the Value1 field where the value in the Dimension is not NULL or NONE. You don't need to tick the 'Suppress When Value Is Null' option for this expression approach to work.

Hope that helps

Kind regards

Steve

Re: BAR CHART, exclude variables in the DIMENSION

svenkita — Thu, 02 Jul 2015 05:18:30 GMT

Attaching is a sample file.

I have created 2 expressions one without NONE & NULL and other one ONLY FOR NONE & NULL

in the Axes tab of properties I have changed the position of one expression on the left and other on the right

Hope this helps

Re: BAR CHART, exclude variables in the DIMENSION

Fri, 03 Jul 2015 01:02:33 GMT

thank you!

Re: BAR CHART, exclude variables in the DIMENSION

Fri, 03 Jul 2015 01:15:05 GMT

You're most welcome. Glad it helped 🙂

topic Re: BAR CHART, exclude variables in the DIMENSION in QlikView

BAR CHART, exclude variables in the DIMENSION

Re: BAR CHART, exclude variables in the DIMENSION

Re: BAR CHART, exclude variables in the DIMENSION

Re: BAR CHART, exclude variables in the DIMENSION

Re: BAR CHART, exclude variables in the DIMENSION

Re: BAR CHART, exclude variables in the DIMENSION