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

# Calculate the Count of Base Customers?

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 …

• ###### Re: Calculate the Count of Base Customers?

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?

Thank you Joanthan for this suggestion.

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?

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.

• ###### Re: Calculate the Count of Base Customers?

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?

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.

• ###### Re: Calculate the Count of Base Customers?

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?

Dates:

Year(Date) as Year,

Month(Date) as Month,

Date(Date) as YearMonth ;

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?

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:

[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:

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:

Year(Date) as Year,

Month(Date) as Month,

Date(Date) as YearMonth ;

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?

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.

Regards,

• ###### Re: Calculate the Count of Base Customers?

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.

• ###### Re: Re: Re: Calculate the Count of Base Customers?

Hi JOnathan,

Many Thanks.

Now I understand why it was not filtering as per the selections. I have added two separate fields now. I am investigating more into that

Also, to answer your first question, (For example 2014 Feb), the distinct Customers = 1 (i.e. Customer No = 10)

• ###### Re: Re: Re: Calculate the Count of Base Customers?

Ok.  The 6 new customers from the sheet i loaded data are as follows for a start date of Feb/2014