End of month totals based on dates around each month end

[Anonymous]

Hello everyone. 

I am trying to calculate the total number of active customers at the end of each month when selecting multiple months.  I am doing this by using the StartDt and DisconnectDt of their service.

Example:

So if a customer has a StartDt of 7/1/2016 and a DisconnectDt of 8/5/2016 they would show as active for Jul-2016 but not for Aug-2016. Even though they were active in August they disconnected before the last day of August.

Currently I am able to do this for the last date in my selection below.  DateKey is the dates in my calendar used in month selections.

sum( {$ <ActivationDt= {"<=$(=date(max(DateKey)))"},[DisconnectDt Closed]= {">$(=date(max(DateKey)))"}>} CustCnt)

AccountID	StartDt	DisconnectDt	DistconnectDt Closed	CustCnt
001	6/2/2015	12/5/2016	12/5/2016	1
002	2/22/2016	null	1/1/2020	1
003	4/12/2016	1/18/2017	1/18/2017	1
004	1/10/2017	1/21/2017	1/21/2017	1
005	1/20/2017	Null	1/1/2020	1
006	7/14/2016	2/2/2017	2/2/2017	1

I created a future date column "DisconnectDt Closed" that has a future disconnect date, "1/1/2020", to deal with the Null value when a customer has not disconnected.  Otherwise the actual disconnect date is populated.

Remember the goal is to display the total number of customers active at the end of each month.  Not just the last month in my selection of months. With a Dimension of Mth-Yr the results with the data above would look like this.

Mth-Yr	MonthEnd Date	Active Customers
Dec-2016	12/31/2016	5
Jan-2017	1/31/2017	3

I have tried different variations of Aggr() expressions with no luck.

Thank you.

[MK_QSL]

For Dec 2016 active customers are 3... not 5.. please recheck and update. (i.e 002, 003, 006)

For Jan 2017 active customers are 3 which is correct.

You can use below script..

Data:
Load
  AccountID,
  If(Len(Trim(StartDt))=0,MonthEnd(Today()),StartDt) as StartDt,
  If(Len(Trim(DisconnectDt))=0,MonthEnd(Today()),DisconnectDt) as DisconnectDt
;
Load
  AccountID,
  Date(Date#(StartDt,'M/D/YYYY')) as StartDt,
  Date(Date#(DisconnectDt,'M/D/YYYY')) as DisconnectDt
Inline
[
  AccountID, StartDt, DisconnectDt
  001, 6/2/2015, 12/5/2016
  002, 2/22/2016,
  003, 4/12/2016, 1/18/2017
  004, 1/10/2017, 1/21/2017
  005, 1/20/2017,
  006, 7/14/2016, 2/2/2017
];

NoConcatenate
Final:
Load *, Date(MonthEndDate,'MMM-YYYY') as MonthYear;
Load
  AccountID,
  MonthEnd(StartDt, (IterNo() - 1)) as MonthEndDate
Resident Data
While MonthEnd(StartDt, (IterNo() - 1)) < MonthEnd(DisconnectDt)
;

Now create a straight table

Dimension

MonthYear

MonthEndDate

Expression

COUNT(DISTINCT AccountID)

Update : File enclosed for your ref..

[Anonymous]

Thank you Manish for taking a look at this.

I think I need to find a solution that calculates this in the chart vs. in load script possibly using Aggr().

Your version works but I run into a couple of issues.

1. The application uses a binary load and takes about 60 seconds to load.  At the time of this post I am at 21 minutes and counting while trying to process all of the date combinations.  We have in our history over 2 million customers and this must create month end dates for each month going back 10 years or 120 months.

2. Unfortunately the way we will need to slice the data, having a single table for the account and their dates would not work in our data model.