topic Re: Re: Re: Base Customers and Last 13 Month Trend Data in QlikView

Base Customers and Last 13 Month Trend Data

dmohanty — Mon, 10 Nov 2014 08:58:48 GMT

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.

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

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!

Re: Base Customers and Last 13 Month Trend Data

SergeyMak — Mon, 10 Nov 2014 09:06:52 GMT

Hi,

Just to clarify: is it possible to modify the load script and data model?

Regards,

Sergey

Re: Base Customers and Last 13 Month Trend Data

dmohanty — Mon, 10 Nov 2014 09:11:43 GMT

Hi Sergey,

Thank you for your interest.

At first place I didn't wanted to alter this script/data model, was wondering if something additional could be done over this.

Still if there are any other approaches to get the same result, you are free to alter the script/model.

After alteration, if you want to validate the result Count of Lost and New Customers, you can check my chart in the app.

Many thanks in advance.

Re: Base Customers and Last 13 Month Trend Data

dmohanty — Mon, 10 Nov 2014 21:10:12 GMT

Hi Sergey,

Did you get a chance to alter the script and help on this request please?

Hi All,

Any other possible solutions please?

Re: Re: Base Customers and Last 13 Month Trend Data

SergeyMak — Tue, 11 Nov 2014 01:31:13 GMT

Hi,

I calculated New, Lost and Active customers. I checked it with data in Excel. It looks ok, but

So, just have a look.

PFA

Re: Base Customers and Last 13 Month Trend Data

dmohanty — Wed, 12 Nov 2014 05:18:04 GMT

Hi All,

Any further approaches/help on this please?

Regards!

Re: Base Customers and Last 13 Month Trend Data

SergeyMak — Wed, 12 Nov 2014 05:20:13 GMT

Hi,

What do you think about my solution?

Regards,

Sergey

Re: Base Customers and Last 13 Month Trend Data

dmohanty — Wed, 12 Nov 2014 05:33:03 GMT

Hey Sergey,

Many thanks for your solution. As stated earlier as well, no doubt your approach is quite good.

But at places, I am unable to find the desired solution. Is the Common Calendar creating issue to filter Start and End date?

Could you please help a bit more?

For example for 2014, this would be the result:

Year	Month	New Customer	Lost Customer
2014	Jan	4	1
	Feb	1	0
	Mar	1	2
	Apr	1	0
	May	0	1
	Jun	2	1
	Jul	0	1
	Aug	0	2
	Sep	0	1
	Oct	1	1
	Nov	1	0

Re: Re: Base Customers and Last 13 Month Trend Data

SergeyMak — Wed, 12 Nov 2014 05:41:34 GMT

I was confused with your NewCustomerKey.

PFA a new version.

I've got results as you need

Regards,Sergey

Re: Re: Re: Base Customers and Last 13 Month Trend Data

dmohanty — Wed, 12 Nov 2014 05:58:31 GMT

Hi Sergey,

Hey many thanks again. Here what you changed ,exactly the same I was thinking. Now the New Customers and Lost Customers results are fine.

Where are the Base Customers you are showing in your model as per the logic stated above?

Meanwhile I have developed this model (attached) to show Lost, New and Base. Could you please tally this with ours to check the Base Count and help me please?

Regards!

Re: Re: Re: Re: Base Customers and Last 13 Month Trend Data

SergeyMak — Wed, 12 Nov 2014 06:13:45 GMT

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

Re: Re: Re: Re: Re: Base Customers and Last 13 Month Trend Data

dmohanty — Wed, 12 Nov 2014 06:22:42 GMT

Hi Sergey,

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

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

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.

Re: Re: Re: Re: Re: Base Customers and Last 13 Month Trend Data

SergeyMak — Wed, 12 Nov 2014 06:27:06 GMT

You confirmed my understanding.

You can find a solution in a previously attached file

Sergey