1 Reply Latest reply: Apr 18, 2018 8:19 AM by Johan Bonerfält RSS

    How to calculate contracted customers per month based on start & end date?

    Johan Bonerfält

      Hi,

      I have a (probably) rather simple problem that I cannot figure out.

       

      I have a data set containing the fields “InstallationNumberID”, “StartDate (YYYY.MM.DD)” and “EndDate (YYYY.MM.DD)”.

       

      InstallationNumberIDStartDate (YYYY.MM.DD)EndDate (YYYY.MM.DD)
      10002017.06.012017.12.31
      10012017.07.012017.11.30
      10022017.06.012017.10.31
      10032017.11.012017.11.30
      10042017.10.012017.11.30
      10052017.08.012017.09.30


      What I need to do is to aggregate and calculate the number of active customers for each month. Something like this:

       

      MonthCount InstallationNumberID in deliveryActive InstallationNumberID

      Jun 2017

      2

      1000,1002
      Jul 201731000,1001,1002
      Aug 201741000,1001,1002,1005
      Sep 201741000,1001,1002,1005
      Oct 201741000,1001,1002,1004
      Nov 201741000,1001,1003,1004
      Dec 201711000
      Jan 20180

      (the column to the right is not necessary to display, it just shows what I imagine the logic would be like)

       

      I have searched but haven’t found. I guess I need to aggregate and use in between for each row. And maybe create/connect to a third, more neutral calender field. Simply put another version of “count active customers”. But how do I get there?

       

      The example data is quite simple. In reality I have several million rows with InstallationNumberID, so the solution must be efficient also for a larger data set.

      Any help would be appreciated!

       

      br,

      J.