Base Customers and Last 13 Month Trend Data

[dmohanty]

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!

[SergeyMak]

Hi,

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

Regards,

Sergey

[dmohanty]

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.

[dmohanty]

Hi Sergey,

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

Hi All,

Any other possible solutions please?

[SergeyMak]

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

[dmohanty]

Hi All,

Any further approaches/help on this please?

Regards!

[SergeyMak]

Hi,

What do you think about my solution?

Regards,

Sergey

[dmohanty]

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

[SergeyMak]

I was confused with your NewCustomerKey.

PFA a new version.

I've got results as you need

Regards,Sergey

[dmohanty]

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!