Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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.

1 Solution

Accepted Solutions
Anonymous
Not applicable
Author

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!

View solution in original post

2 Replies
Anonymous
Not applicable
Author

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!

Obs_vdf_paris
Contributor
Contributor

hi i have the same probléme 

can you explain me please how you do solution