5 Replies Latest reply: Nov 10, 2014 8:39 AM by Maicon Mello RSS

    Problem filling values in empty fields

      Hi all,

       

      I'm pretty new to scripting in Qlikview and am having trouble bring together data from two tables to show new customer run-off. 

       

      My company sells to new customers who sign up for our service and we want to follow up on the drop-off rates as time goes by.  So in one table i have the original number of customers signed up and in the second table I have the number of customers remaining from the original sales month.

       

      So far so good but I need to add the original sales number even to those months that there is no customers left. So if we sell to 100 customers in Jan, and the remaining customers number is 100 in Jan, 80 in Feb, 60 in Mar & 0 in April I need to connect the original sales number to April despite having a null value for April. 

       

      I've created an example where sales from Feb 2013 have no remaining customers in after 6 or 7 months for Product A in Country Y.

       

      SALES_DATEMONTHS_SINCE_SALES1234567
      2013-02-01NO OF SALES12 00012 00012 00012 00012 000--
      2013-02-01NO. REMAINING12 00010 2008 6707 3706 264--
      2013-02-01%100,0%85,0%72,3%61,4%52,2%--

       

      This should look like this:

       

      SALES_DATEMONTHS_SINCE_SALES1234567
      2013-02-01NO OF SALES12 00012 00012 00012 00012 00012 00012 000
      2013-02-01NO. REMAINING12 00010 2008 6707 3706 264--
      2013-02-01%100,0%85,0%72,3%61,4%52,2%0,0%0,0%

       

       

      I've tried both Peek () and iterco() & while but am to unfamiliar with these to understand how to use it. 

       

      The goal is to be able to look at a chart which plots % remaining on the Y axis with MONTHS_SINCE_SALES on the X axis and to be able to drill down on this.

       

      Any of you experts out there know how to help me out?

       

      Any advice much appreciated!