2 Replies Latest reply: Feb 28, 2017 9:04 PM by Darrin Pilkington RSS

    End of month totals based on dates around each month end

    Darrin Pilkington

      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)


      AccountIDStartDtDisconnectDtDistconnectDt ClosedCustCnt
      001

      6/2/2015

      12/5/201612/5/20161
      0022/22/2016null1/1/20201
      0034/12/20161/18/20171/18/2017

      1

      0041/10/20171/21/20171/21/20171
      0051/20/2017Null1/1/2020

      1

      0067/14/20162/2/20172/2/20171


      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-YrMonthEnd DateActive Customers
      Dec-2016

      12/31/2016

      5
      Jan-20171/31/20173

       

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

       

      Thank you.

        • Re: End of month totals based on dates around each month end
          Manish Kachhia

          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..

            • Re: End of month totals based on dates around each month end
              Darrin Pilkington

              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.