Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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,
J.
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!
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!
hi i have the same probléme
can you explain me please how you do solution