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 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.
For the first question, to count customers I am counting the unique number of [Customer Nos].
count( distinct [Customer No])
What i noticed at the bottom of the screenshot, there are 6 records but the customer no is the same in all the records... is that one customer or six ? If it is six, please tell me how to uniquely identify a customer ? If its the combination of customer no and contract for example you would adjust the expression as follows:
count( distinct [Customer No] & [Contract No] )
For the 2nd question, there are 3 date dimensions. Cust Start Date , Cust End Date and YearMonth (which is actually a date and has Year and Month as separate fields)
When you select Month, it is going to filter for all customers that are ACTIVE... any customer that has cust start the same or less than the selected month and any customer that has a cust end same or after the selected month. So you will see cust start dates that are OUTSIDE the selected month. That is how interval matching works.
If you want to select a month and show only the customers that started in that month, you need to add a new field in the load that calculates the month (and perhaps year) off the cust start date .
For example, augment this:
Date([Cust Start Date]) as [Cust Start Date],
Date([Cust End Date]) as [Cust End Date],
to:
Date([Cust Start Date]) as [Cust Start Date],
Year(Date([Cust Start Date])) as [Cust Start Year],
Month(Date([Cust Start Date])) as [Cust Start Month],
Date([Cust End Date]) as [Cust End Date],
Year(Date([Cust End Date])) as [Cust End Year],
Month(Date([Cust End Date])) as [Cust End Month],
Then in the UI, filter on [Cust Start Month] and [Cust Start Year] .... it will associate how you want.
Hi JOnathan,
Many Thanks.
Now I understand why it was not filtering as per the selections. I have added two separate fields now. I am investigating more into that
Also, to answer your first question, (For example 2014 Feb), the distinct Customers = 1 (i.e. Customer No = 10)
Ok. The 6 new customers from the sheet i loaded data are as follows for a start date of Feb/2014