Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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