Rolling count of active leasing contracts

[peterstalberg]

Hi Guys,

Here is a sample table with leasing contracts. Each contract has a unique ID and a start & end date.

CONTRACT_ID	FROM_DATE	TO_DATE
…
1254	13/11/2019	16/4/2020
3678	1/1/2020	6/4/2020
2345	5/2/2020	10/3/2020
1278	1/1/2020	20/3/2020
1359	5/3/2020	1/6/2020
1623	1/4/2020	16/6/2020
1297	16/2/2020	17/4/2020
…

In a bar chart I want to visualize the number (count) of active contracts (active only part of a month is consider as active) per month in a rolling (i.g. 12 months) period. Please refer to enclosed sample (although only showing 8 months :-)). 

To clarify, contract 1254 is active in November, December, Januari, February, March and April.

Having studied some other posts I believe this can be achieved with IntervallMatch or similar function(s) within the script  but is there a possibility to achieve  this directly in a chart function via set analysis etc.? 

I'm fairly new to QS hence a working example on how to solve this would be highly appreciated.

Thanks!//Peter 

[GaryGiles]

The best way to do would be to set up a date table via the load script.

If you are set on doing this in chart without alternating the load script, you could create a variable

vDateRange as

valuelist(Date('1/1/2020')
,Date('2/1/2020')
,Date('3/1/2020')
,Date('4/1/2020')
,Date('5/1/2020')
,Date('6/1/2020')
,Date('7/1/2020')
,Date('8/1/2020')
,Date('9/1/2020')
,Date('10/1/2020')
,Date('11/1/2020')
,Date('12/1/2020'))

For your dimension, use:  =$(vDateRange)

For you measure, use: 

count(if(TO_DATE>=MonthStart($(vDateRange)) AND
FROM_DATE<=MonthEnd($(vDateRange)),CONTRACT_ID))

Not very dynamic, but there might be some ways to tweak it.

[peterstalberg]

Hi Gary,

Thank you for your response. This might work but I think I'm looking for a more dynamic solution. The data spans over several years and new leasing contracts are added on a daily basis. Rgds//Peter