topic Re: Set analysis with Missing Values in QlikView

Set analysis with Missing Values

Wed, 02 Sep 2015 12:01:25 GMT

Hi,

i have this situation :

CD_CODE	Prezzo2014	Prezzo 2015	Qty 2015	Prezzo x Qty 2015	Coverage
S0440871	-	13,64	-12	-164	-€ 164
S0302446	-	8,76	7	61	€ 35

When Prezzo2014 is equal to '-' , I want that Coverage is equal to zero.

How can i do?

I tried with Isnull Function or Len function but doesn't work.

Thanks a lot

Re: Set analysis with Missing Values

jonathandienst — Wed, 02 Sep 2015 12:11:52 GMT

Is Prezzo2014 = to a hyphen character, or a null? I expect the latter. Use this amended expression for Coverage:

=If(Len(Prezzo2014) = 0, 0, <existing expression for Coverage>)

Re: Set analysis with Missing Values

sunny_talwar — Wed, 02 Sep 2015 12:12:06 GMT

Are you doing this in the script or a front end chart?

Re: Set analysis with Missing Values

Wed, 02 Sep 2015 12:14:53 GMT

Front end Linear Table

Re: Set analysis with Missing Values

Wed, 02 Sep 2015 12:35:39 GMT

I have tried similar suggestions to Jonathan on the past but I have also put the expression ion the load script and set a value in a new field to either 1 or zero and then used the new field in my set analysis. In the example below, my set analysis would be based off the value of the field 'NullCustomer'

e.g. If(Len(Trim([Customer #]))=0,0, 1) as [NullCustomer]

Re: Set analysis with Missing Values

Kushal_Chawda — Wed, 02 Sep 2015 12:46:06 GMT

Create the expression for Coverage as below

if(len(trim([Prezzo2014]))=0 or [Prezzo2014]=0,0,YourExpressionForCoverage)

Re: Set analysis with Missing Values

sasiparupudi1 — Wed, 02 Sep 2015 13:04:14 GMT

what is your expression for coverage?

Re: Set analysis with Missing Values

Anonymous — Wed, 02 Sep 2015 13:17:12 GMT

Hi ,

try as below

if(IsNull(PREZZO2014),0,1)

Re: Set analysis with Missing Values

avinashelite — Wed, 02 Sep 2015 13:22:34 GMT

Hi,

This is the case of missing values we cannot handle it with len or null functions . you need to handle this in the script only ..one easy solution would be go to chart Properties>Presentation> Missing symbol will be like - change it to 0.

NOTE: Try the chart presentation option if your handling the null in dimension

Re: Set analysis with Missing Values

Wed, 02 Sep 2015 13:51:45 GMT

I have successfully done the following in the script, which will then allow me to use set analysis

In the example below, my set analysis would be based off the value of the field 'NullCustomer'

e.g. If(Len(Trim([Customer #]))=0,0, 1) as [NullCustomer]

Re: Set analysis with Missing Values

Thu, 03 Sep 2015 07:40:25 GMT

I tried with your expression but there is a problem. I have a drill down group in my linear table and when click on the row to drill total result change. What is it the problem?