12 Replies Latest reply: Nov 10, 2014 12:10 PM by Jonathan Poole RSS

    Calculate the Count of Base Customers?

    Dicky Mohanty

      Hi,

       

      We have a requirement, where we are calculating the New Customers and Lost Customers based on the Cust_Start_Date and Cust_End_Date fields.

      These has been implemented in the attached QVW. Also a sample excel data set (used in QVW) is attached.

       

      Now we need to show the count of Base Customers for every month (Jan - Dec). Logic is as below:

      Count of customer whose Start Date is less than particular month (ex: Feb in below table) and End Date is greater than for that month.

      CustNo

      StartDate

      EndDate

      1

      1 Jan 2014

      31 Dec 2014

      2

      1 Feb 2014

      31 Dec 2014

      3

      12 Jan 2014

      31 Oct 2014

      4

      1 Mar 2014

      31 Dec 2014

      5

      1 May 2014

      1 Aug 2014

      6

      2 Feb 2014

      31 Dec 2014

      7

      13 Mar 2014

      31 Dec 2014

      8

      1 April 2014

      31 Dec 2014

       

      Base Customer for Feb = 2, March = 4, April = 6 , May = 7 , June = 8 , July = 8 , Aug = 7 and so on …

       

       

      How this can be done with the already done scripting? Or any other approaches available? Please help.

        • Re: Calculate the Count of Base Customers?
          Dicky Mohanty

          Any help on this please???

            • Re: Calculate the Count of Base Customers?
              Jonathan Poole

              Hi this BEGS for an IntervalMatch solution which requires a change to your load script / data model. 

               

              Are you open to that  ?

               

              Here is the main blog post. Use whenever you have start/end dates and need to know if that interval falls within a specific date

               

              http://community.qlik.com/blogs/qlikviewdesignblog/2013/04/04/intervalmatch

               

              What you would do is built a master date calendar that includes all dates (or just months) between the min and max date in your date set. That is the primary date.

               

              Then you load the start/end dates (and accompanying information like customer number) and do an interval match that connects both loads together.

                • Re: Calculate the Count of Base Customers?
                  Dicky Mohanty

                  Thank you Joanthan for this suggestion.

                   

                  Could you please help in achieving that?

                   

                  Also, We had created two tables Lost Customer and New Customer, because we have KPIs related to that. That's why separate dates are stored in two different tables.

                   

                  Can you please help in achieving Lost Customers, New Customers and Base Customers using your way please? You are free to alter the script/data model. It would be a great help!

                    • Re: Re: Calculate the Count of Base Customers?
                      Jonathan Poole

                      Here is a possible solution.

                       

                      It works but i'm not in love with the UI performance.  Can you check to see if its accurate ?  If you find it slow let me know and i'll work to improve it.

                       

                      Capture.PNG.png

                        • Re: Calculate the Count of Base Customers?
                          Dicky Mohanty

                          Hi Jonathan,

                           

                          Thank you for a helping hand. The scripting part I understood. But the data in the report is incorrect and the UI is bit slow for me too.

                          Can you please look into again?

                           

                          For example 2014 Jan, New Customer = 4, Lost Customer = 1 .

                            • Re: Re: Calculate the Count of Base Customers?
                              Jonathan Poole

                              Hi -  actually i got the 3 and 2 numbers from your spreadsheet. Below are the filtered results from excel that back up the numnbers in qlik .  The first screenshot filtered in excel with  cust start date = 2014 Jan. The 2nd screenshot has cust end date = 2014 Jan. 

                               

                              for the performance issue , I resolved it by doing the interval match at the month level ... so if a customer joined anytime in the month it will show as a new customer for the month, (same for lost customer).  Reattached is the QVW.

                               

                               

                               

                              Capture.PNG.png

                               

                              Capture1.PNG.png

                                • Re: Calculate the Count of Base Customers?
                                  Dicky Mohanty

                                  Hi Jonathan,

                                   

                                  At first place I apologize for assuming your data to be wrong. It was right, in fact instead of Contract table form excel, you had used Customers.

                                  Thank you for the help. No issues. I have changed the table (Customers to Contract) and added the fields in QV Script. But somehow we are missing the 2013 and 2014 data after reloading. But when I am changing the Autogenerate to somewhat around 80, I am getting data. What could be the best way not to miss the years?

                                  Is it because of Synthetic Key? Could you please help me more in evaluating?

                                   

                                  Also, please help me in explain this section below:

                                   

                                  Dates:

                                  Load

                                    Year(Date) as Year,

                                    Month(Date) as Month,

                                    Date(Date) as YearMonth ;

                                  Load

                                    MakeDate( if( mod( RecNo(),12)= 0,  Year($(vMinDate))-1 + floor(RecNo()/12),  Year($(vMinDate)) + floor(RecNo()/12) ) ,  if( mod( RecNo(),12) = 0, 12,mod( RecNo(),12))  ,15) as Date

                                   

                                   

                                  AutoGenerate  48 ;

                                   

                                   

                                  IntervalMatch (YearMonth) LOAD [Start Date], [End Date] Resident Intervals;

                                    • Re: Re: Calculate the Count of Base Customers?
                                      Jonathan Poole

                                      Its probably because i hard coded the solution to just 4 years (48 months) with this line of code:

                                       

                                      autogenerate 48;

                                       

                                      You could change that to 72 to add 2 more years of data, or use the updated script in the attached QVW which will calculate the (MaxYear - MinYear+1  ) * 12 ... to calculate dynamically the number of months that exist in your data (based on values in Cus Start Date)

                                      -----------------------

                                       

                                      Intervals:

                                      LOAD [Customer No],

                                           [Cust Name],

                                           Date([Cust Start Date]) as [Cust Start Date],

                                           Date([Cust End Date]) as [Cust End Date],

                                           MonthStart([Cust Start Date]) as [Start Date] ,

                                           MonthEnd([Cust End Date]) as  [End Date],

                                           Age,

                                           [Cust Segment]

                                      FROM

                                      [C:\Temp\New_Lost_Customers.xlsx]

                                      (ooxml, embedded labels, table is Customer);

                                       

                                      temp:

                                      load

                                        min([Start Date]) as MinDate,

                                        max([Start Date]) as MaxDate

                                      Resident Intervals;

                                       

                                      let vMinDate=peek('MinDate',0,'temp') ;

                                      let vMaxDate=peek('MaxDate',0,'temp') ;

                                       

                                      drop table temp;

                                       

                                      Dates:

                                      Load

                                        Year(Date) as Year,

                                        Month(Date) as Month,

                                        Date(Date) as YearMonth ;

                                      Load

                                        MakeDate( if( mod( RecNo(),12)= 0,  Year($(vMinDate))-1 + floor(RecNo()/12),  Year($(vMinDate)) + floor(RecNo()/12) ) ,  if( mod( RecNo(),12) = 0, 12,mod( RecNo(),12))  ,15) as Date

                                       

                                      AutoGenerate  12* (Year($(vMaxDate)) - Year($(vMinDate))+1) ;

                                       

                                      IntervalMatch (YearMonth) LOAD [Start Date], [End Date] Resident Intervals;

                                        • Re: Re: Re: Calculate the Count of Base Customers?
                                          Dicky Mohanty

                                          Hi Jonathan,

                                           

                                          Thank you very much for the pain again. I got the script now.

                                           

                                          But there is some discrepancy in the results. Please see the attached image (Top one is your result and below one is mine with Excel data).

                                           

                                          For example,

                                          For 2014 Feb, there is only one New Customer i.e Cust No = 10.

                                          In your app, it is 6. Also, when I am filtering the Month and Year, the Customer Start Date list box is not showing right associated values for Feb 2014.

                                           

                                          Could you please help in checking once again?

                                           

                                          Regards,

                                            • Re: Calculate the Count of Base Customers?
                                              Jonathan Poole

                                              For the first question, to count customers I am counting the unique number of [Customer Nos].

                                               

                                              count( distinct [Customer No])

                                               

                                              What i noticed at the bottom of the screenshot, there are 6 records but the customer no is the same in all the records... is that one customer or six ?  If it is six, please tell me how to uniquely identify a customer ?  If its the combination of customer no and contract for example you would adjust the expression as follows:

                                               

                                              count( distinct [Customer No] & [Contract No] )

                                               

                                              For the 2nd question, there are 3 date dimensions.  Cust Start Date  ,  Cust End Date and YearMonth (which is actually a date and has Year and Month as separate fields)

                                               

                                              When you select Month, it is going to filter for all customers that are ACTIVE... any customer that has cust start the same or less than the selected month and any customer that has a cust end same or after the selected month.  So you will see cust start dates that are OUTSIDE the selected month. That is how interval matching works.

                                               

                                              If you want to select a month and show only the customers that started in that month, you need to add a new field in the load that calculates the month (and perhaps year) off the cust start date .

                                               

                                              For example,   augment this:

                                               

                                                   Date([Cust Start Date]) as [Cust Start Date],

                                                   Date([Cust End Date]) as [Cust End Date],

                                               

                                              to:

                                               

                                                   Date([Cust Start Date]) as [Cust Start Date],

                                                   Year(Date([Cust Start Date])) as [Cust Start Year],

                                                   Month(Date([Cust Start Date])) as [Cust Start Month],

                                                   Date([Cust End Date]) as [Cust End Date],

                                                   Year(Date([Cust End Date])) as [Cust End Year],

                                                   Month(Date([Cust End Date])) as [Cust End Month],

                                               

                                              Then in the UI, filter on [Cust Start Month] and [Cust Start Year] .... it will associate how you want.