Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
Hi,
Just to clarify: is it possible to modify the load script and data model?
Regards,
Sergey
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.
Hi Sergey,
Did you get a chance to alter the script and help on this request please?
Hi All,
Any other possible solutions please?
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
Hi All,
Any further approaches/help on this please?
Regards!
Hi,
What do you think about my solution?
Regards,
Sergey
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 |
I was confused with your NewCustomerKey.
PFA a new version.
I've got results as you need
Regards,Sergey
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!