each row of data as input is a study, with a start date and an end date.
The indicator I need is the number of studies running, during each month, or each year.
So a study starting on 2016/01/12 and ending on 2016/03/21 would count :
- for 1 on dimension Month = january
- for 1 on dimension Month = february
- for 1 on dimension Month = march
- for 3/12 on dimension Year = 2016
Therefore I was planning to duplicate the rows to have them at month level, and to just count the rows to calculate the indicator (and divide by 12 if at Year level)
Thank you Khawar. I didn't know that one and it is very interesting. However I don't see how I could make use of it in my case, because I don't have a crosstable, and I cannot turn my data into a crosstable.
Indeed I can't predefine the number of month between the start date and the end date, so I wouldn't know how many columns of months I would need in my crosstable.
I'm looking at intervalmatch though, it looks like something I could use!
one solution to just duplicate the source rows with different months could be:
LOAD RecNo() as ID, *, MonthName(StartDate,IterNo()-1) as Month INLINE [ StartDate, EndDate 2016/01/12, 2016/03/21 2016/02/10, 2016/05/26 2016/03/01, 2016/04/05 2016/01/12, 2016/03/21 ] While MonthName(StartDate,IterNo()-1)<=EndDate;
But your description sounds like linking your table with a master table using the intervalmatch prefix is the better idea.
hope this helps
QlikCommunity_Thread_243879.qvw 154.0 K