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
Update : File enclosed for your ref..
Community_248578.qvw 155.8 K
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.