topic Re: Null values in a calculated dimension in QlikView

Null values in a calculated dimension

Fri, 08 Jul 2016 11:14:15 GMT

Hi is there a way to drill down into the null values in a chart which uses a calculated dimension. I have a pie chart with the following dimension which picks out only the values labelled 'High' and 'Low'. I want to be able to drill into the rest of the data which doesnt have a TAX_BAND assigned.

=if(not wildmatch(TAX_BAND, '*Low*', '*High*'), null(), TAX_BAND) as NEW_TAX_BAND

Re: Null values in a calculated dimension

sunny_talwar — Fri, 08 Jul 2016 11:16:13 GMT

May be don't make it null() call it NULL

=If(not WildMatch(TAX_BAND, '*Low*', '*High*'), 'NULL', TAX_BAND)

Re: Null values in a calculated dimension

Fri, 08 Jul 2016 11:20:11 GMT

Thanks Sunny. It doesnt seem to like that expression. Throwing an error.

Re: Null values in a calculated dimension

trdandamudi — Fri, 08 Jul 2016 11:21:53 GMT

Give a try on the below for calculated dimension:

=If(Len(Trim(TAX_BAND))>0 ,TAX_BAND,'Null')

Re: Null values in a calculated dimension

sunny_talwar — Fri, 08 Jul 2016 11:24:58 GMT

What is the error that you are seeing?

Re: Null values in a calculated dimension

Fri, 08 Jul 2016 11:26:37 GMT

'Error in calculated dimension' appears in pie chart

Re: Null values in a calculated dimension

sunny_talwar — Fri, 08 Jul 2016 11:29:24 GMT

Would you be able to share a snapshot of the expression you used for the dimension?

Re: Null values in a calculated dimension

Fri, 08 Jul 2016 11:29:55 GMT

I beg your pardon. That works now. However I see both 'NULLS' and a separate chunk in the pie chart which is all values with a TAX_BAND assigned aside from the ones picked up by the wild card. Ideally I want to see only 3 chunks.

High

Low

NULL ( ie those values with no tax band assigned )

Re: Null values in a calculated dimension

trdandamudi — Fri, 08 Jul 2016 11:30:38 GMT

Please check the expression that Sunny provided and it is working. Please see the chart below with Sunny expression:

Re: Null values in a calculated dimension

sunny_talwar — Fri, 08 Jul 2016 11:32:48 GMT

How about you try this (I removed not before WildMatch and changed the order of True and False):

=If(WildMatch(TAX_BAND, '*Low*', '*High*'), TAX_BAND, 'NULL')

Re: Null values in a calculated dimension

Sun, 10 Jul 2016 22:41:49 GMT

Thanks Sunny. This approach partially works. Let me clarify I have the following

TAX_BAND

Low

High

Medium

Top

It shows three chunks ie Low, High and Null. The issue is the 'Null' values are aggregated along with 'Medium' and 'Top' also. Only when you click into the null chunk does it then correctly drill into the values only for the nulls. To confirm I want 3 chunks in my pie chart - Low, High and Nulls. The 'Medium' and 'Top' should be effectively completely ignored

Re: Null values in a calculated dimension

sunny_talwar — Mon, 11 Jul 2016 13:43:31 GMT

Try this:

=If(WildMatch(TAX_BAND, '*Low*', '*High*'), TAX_BAND, If(Len(Trim(TAX_BAND)) = 0, 'NULL'))