topic Re: How can I avoid displaying null values? in QlikView

How can I avoid displaying null values?

Mon, 10 Feb 2014 21:52:05 GMT

Is there a way to restrict the output of a chart to only those values that are not null?

The users at my company want to take a look at policy retention in the form of a Triangle, which looks something like this:

Starting Month	Initial Value	One Month	Two Months	Three Months	Four Months
Jan 2013	10	9	8	7	5
Feb 2013	11	10	8	7	6
Mar 2013	12	10	9	8
Apr 2013	12	11	10
May 2013	11	10
Jun 2013	11

Each policy is assigned to a group based on what month the customer signed up, and then tracked until they are no longer with the company.

I have been asked to provide a graph of the "Lifetime Estimate", which is an equation that basically asks, 'on average, what percentage of initial customers make it to the rightmost edge of the triangle?' For Jun 2013, it would be the red numbers above over their initial values, or (11/11 + 10/11 + 10/12 + 8/12 + 6/11); for May 2013 it would be the blue numbers over their initials, or (11/11 + 11/12 + 9/12 + 7/11 +5/10), one diagonal behind the rightmost edge. Unfortunately, Qlikview doesn't seem to be particularly well equipped to handle this particular type of data structure. In particular, if I try to put this in a chart using [Starting Month] as my dimension, I would have to write it as:

sum([Initial Value])/sum([Initial Value]) + above(sum([One Month])/sum([Initial Value])) +

above(sum([Two Months])/sum([Initial Value]),2) + above(sum([Three Months])/sum([Initial Value]),3) +

above(sum([Four Months])/sum([Initial Value]),4).

This works, except that for Jan 2013- Apr 2013, above(function, 4) doesn't exist, so the equation breaks down. What I wind up with is a chart that looks like this:

Starting Month	Lifetime Estimate
Jan 2013	-
Feb 2013	-
Mar 2013	-
Apr 2013	-
May 2013	3.80
Jun 2013	3.95

Is there a way to avoid having the blanks at the beginning? I have a workaround using pick(match()) but unfortunately this is running very slowly.

Thanks,

Kevin

Re: How can I avoid displaying null values?

swuehl — Mon, 10 Feb 2014 22:11:01 GMT

Using rangesum() instead of the addition operator + should be more stable when handling NULL as operands:

rangesum(

sum([Initial Value])/sum([Initial Value]),

above(sum([One Month])/sum([Initial Value])),

above(sum([Two Months])/sum([Initial Value]),2),

above(sum([Three Months])/sum([Initial Value]),3),

above(sum([Four Months])/sum([Initial Value]),4)

)

Re: How can I avoid displaying null values?

Mon, 10 Feb 2014 22:20:03 GMT

Hi Suwehl,

I am actually using rangesum() in the application already. Unfortunately, it does not solve the problem of QlikView needing to populate the dimension field for the earlier months in the chart object in order to be able to calculate the equation.

To put it another way, having a NULL value there is actually the DESIRED result, as when there is not enough data for me to get all five elements in the equation, I do not want it to return a result, as that will confuse users. (If the equation was applied to the 'Jan 2013' row and NULL values were assigned a value of 0, the equation would return (10/10+0+0+0+0) = 1, which would suggest very poor customer retention in that month - something that simply isn't true.)

Re: How can I avoid displaying null values?

Anonymous — Mon, 10 Feb 2014 22:21:34 GMT

Hi Kevin,

To display only non-null values,In straight table - presentation tab - select suppress zero values check-box.

Re: How can I avoid displaying null values?

Mon, 10 Feb 2014 22:31:35 GMT

Sorry Nitha, that doesn't seem to work - I think it's due to the particular way the Above() function works.