Count if date is within period

[_Scott_]

The Data:

• We have users identified by 'userID'
• Users have a membership that starts on 'startDate' and ends on 'endDate'
• I have a "calendar" table to show all dates. It's a single column with all dates ranging from min(startDate) to max(endDate)
• This calendar is in it's own table and is not joined to the membership table. I understand this is probably the problem, however I don't know how I would join the 3 dates.
• I am only using the Data Manager but will use the Data Load Editor if I need to

The Goal: 

I would like to have a chart that shows how many members we had within a given period. For example, the dimension would be 'date' and the measure would be 'count if  (startDate <= date <= endDate)'.

I would also like the date to be aggregable e.g. monthly, quarterly, yearly. I would also like the data to be responsive to filters e.g. if the users are filtered by 'Age = x'.

What I've Tried:

I have tried creating a bar chart using the set expression:

• Count({$< [Date] = {'>=$(=[Cert Start]) <=$(=[Cert End])'}>} [Cert ID]) 

However all bars end up being the same height.

[MayilVahanan · Accepted solution]

HI @_Scott_ 

We need to use intervalmatch concept. 

For your Reference: 

https://community.qlik.com/t5/QlikView-Documents/Intervalmatch-example-with-dates/ta-p/1488212

https://community.qlik.com/t5/Qlik-Design-Blog/Using-IntervalMatch/ba-p/1475510

https://community.qlik.com/t5/New-to-QlikView/What-is-interval-match/td-p/88588

https://help.qlik.com/en-US/sense/September2020/Subsystems/Hub/Content/Sense_Hub/LoadData/matching-i...

[_Scott_]

Brilliant! That is exactly what I was looking for.