Base Customers and Last 13 Month Trend Data - Page 2 - Qlik Community

dmohanty · ‎2014-11-10

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 the sample data source (Contract Tab) is attached.

Req 1

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.

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

Req 2

Also, Need the last 13 month trend for the Lost, New and Base Customers, on single period dimension

How this can be implemented with the already done scripting? Please help.

Regards!

SergeyMak · ‎2014-11-12

Just to clarify: 'Base Count' means Number of Active Customers in the period.

For Example: Customer1 Start 01/01/2014 End 31/03/2014

It should be count as 1 in January, February and March.

Is it correct?

If so - PFA

Regards,

Sergey

Regards,
Sergey

dmohanty · ‎2014-11-12

Hi Sergey,

let me help you bit more in explaining the Base Customer concept. For example for below sample table:

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.

For February, the Base Customer = 2 (Start date is less than Feb and end date is greater than Feb, means CustNo =1 and CustNo =3)

For August, the base customer =7 (All except CustNo =5 as the end date is not greater than August)

Hope it is more clear for you now.

SergeyMak · ‎2014-11-12

You confirmed my understanding.

You can find a solution in a previously attached file

Sergey

Regards,
Sergey