Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
peterstalberg
Contributor III
Contributor III

Rolling count of active leasing contracts

Hi Guys,

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

CONTRACT_IDFROM_DATETO_DATE
  
125413/11/201916/4/2020
36781/1/20206/4/2020
23455/2/202010/3/2020
12781/1/202020/3/2020
13595/3/20201/6/2020
16231/4/202016/6/2020
129716/2/202017/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 

Labels (2)
2 Replies
GaryGiles
Specialist
Specialist

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
Contributor III
Contributor III
Author

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