topic Re: count per month between dates in QlikView

count per month between dates

Wed, 04 Jul 2012 13:03:40 GMT

I have contracts with a startDate and endDate.

Id | startDate | endDate

1 | 01.01.2011 | 31.12.2011

2 | 01.05.2011 | 31.12.2011

3 | 01.01.2011 | 31.12.2012

4 | 01.05.2011 | 31.12.2012

Now I need to count the active contracts per month.

Result Diagram:

01/2011 : 2

02/2011 : 2

03/2011 : 2

04/2011 : 2

05/2011 : 4

06/2011 : 5

…

How can I achieve that ?

Many thanks,

Marcel

Re: count per month between dates

swuehl — Wed, 04 Jul 2012 13:08:55 GMT

Are your startDate always at month's start and endDate always on month's end?

You could create a Table with Month start dates, then use an INTERVALMATCH to link your Month date table to the ID periods. Please check INTERVALMATCH LOAD prefix in the Help file.

Regards,

Stefan

Re: count per month between dates

Wed, 04 Jul 2012 13:17:47 GMT

sadly the start and end dates can be any date.

Re: count per month between dates

swuehl — Wed, 04 Jul 2012 13:19:51 GMT

So does a contract count to July if the contract ends somewhere in the middle? Are there any additional rules?

My suggested approach should work if you are saying 'Nr of contracts at a certain date', then create the table of e.g. Month start dates.

Re: count per month between dates

Wed, 04 Jul 2012 13:45:46 GMT

swuehl wrote:
So does a contract count to July if the contract ends somewhere in the middle? Are there any additional rules?
My suggested approach should work if you are saying 'Nr of contracts at a certain date', then create the table of e.g. Month start dates.

yes, a contract counts to July if it ends somewhere in the middle.

I did look at the INTERVALMATCH and it looks promising. But I actually have 2 date ranges (start/end of contract and start/end of month) and the INTERVALMATCH only checks if a single value is within a range.

Or maybe I don't get it... 😞

Re: count per month between dates

swuehl — Wed, 04 Jul 2012 13:58:27 GMT

I still believe an Intervalmatch should work, but maybe there is an easier solution for you.

Try something like

TEST:

LOAD *,

addmonths(monthstart(startDate),iterno()-1) as ContractMonth

while monthstart(endDate) >= addmonths(monthstart(startDate),iterno()-1);

LOAD * INLINE [

Id , startDate , endDate

1 , 01.01.2011 , 31.12.2011

2 , 01.05.2011 , 31.12.2011

3 , 01.01.2011 , 31.12.2012

4 , 01.05.2011 , 31.12.2012

5 , 20.02.2011 , 1.12.2011

];

And then create a table with ContractMonth as dimension (or =MonthName(ContractMonth), if you like), and then use

=count(Id)

as expression.

Re: count per month between dates

Wed, 04 Jul 2012 15:27:41 GMT

cool! it works! It generates a lot of data, but it works.

many thanks!