4 Replies Latest reply: Feb 10, 2014 5:31 PM by Kevin Flynn RSS

    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.



        • Re: How can I avoid displaying null values?
          Stefan Wühl

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



          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?

              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?
              neetha P

              Hi Kevin,


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