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

Anonymous — Mon, 09 Apr 2018 14:10:07 GMT

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

InstallationNumberID	StartDate (YYYY.MM.DD)	EndDate (YYYY.MM.DD)
1000	2017.06.01	2017.12.31
1001	2017.07.01	2017.11.30
1002	2017.06.01	2017.10.31
1003	2017.11.01	2017.11.30
1004	2017.10.01	2017.11.30
1005	2017.08.01	2017.09.30

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

Month	Count InstallationNumberID in delivery	Active InstallationNumberID
Jun 2017	2	1000,1002
Jul 2017	3	1000,1001,1002
Aug 2017	4	1000,1001,1002,1005
Sep 2017	4	1000,1001,1002,1005
Oct 2017	4	1000,1001,1002,1004
Nov 2017	4	1000,1001,1003,1004
Dec 2017	1	1000
Jan 2018	0

(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,

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

Anonymous — Wed, 18 Apr 2018 12:19:01 GMT

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!

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

Obs_vdf_paris — Sun, 05 Apr 2020 10:04:49 GMT

hi i have the same probléme

can you explain me please how you do solution

topic How to calculate contracted customers per month based on start & end date? in App Development

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

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

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