Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

sundqjoh
New Contributor

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
sundqjoh
New Contributor

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!

1 Reply
sundqjoh
New Contributor

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!

Community Browser