Calculate the Count of Base Customers? - Qlik Community

dmohanty · ‎2014-11-06

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.

dmohanty · ‎2014-11-06

Any help on this please???

JonnyPoole · ‎2014-11-06

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.

dmohanty · ‎2014-11-06

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!

JonnyPoole · ‎2014-11-06

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.

dmohanty · ‎2014-11-06

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 .

JonnyPoole · ‎2014-11-07

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.

dmohanty · ‎2014-11-07

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;

JonnyPoole · ‎2014-11-10

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

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

dmohanty · ‎2014-11-10

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,