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

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

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.

• ###### Re: How to calculate contracted customers per month based on start & end date?

For future reference, if someone stumbles upon my post with a similar problem - the function IntervalMatch in cooperation with a new master calendar solved it!