Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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..
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.