# QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Rules, plus terms and conditions, can be found here.
Valued Contributor

## 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 …

12 Replies
Valued Contributor

Employee

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

Valued Contributor

## 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!

Employee

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

Valued Contributor

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

Employee

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

Valued Contributor

## 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;

Employee

## 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

(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;

Valued Contributor

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