Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join us for a live Q&A! September 21, 10 AM ET - Onboarding Fast in Qlik Sense SaaS! REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

How can I avoid displaying null values?

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 MonthInitial ValueOne MonthTwo MonthsThree MonthsFour Months
Jan 2013109875
Feb 20131110876
Mar 2013121098
Apr 2013121110
May 20131110
Jun 201311

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 MonthLifetime Estimate
Jan 2013-
Feb 2013-
Mar 2013-
Apr 2013-
May 20133.80
Jun 20133.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

4 Replies
swuehl
MVP
MVP

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)

)

Not applicable
Author

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.)

neetha_p
Master
Master

Hi Kevin,

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

Not applicable
Author

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