Count for disconnected calendar table

[dmeeuws]

Hi all,

I've been switching from Power BI to Qlik and am struggling with the following problem. I prefer to solve it from Qlik side instead of from database side.

I have a table with projects:

Project ID | Start Date | End Date

1 | 01-01-2020 | 31-05-2020

2 | 10-02-2020 | 27-09-2020

3 | 10-10-2020 | 31-12-2020

Ideally I want to make a table that looks like this:

# of active projects: amount of project id's where start_date <= last_day_of_month AND end_date >= last_day_of_month

Year | Month (reference date will be last day of month) | # of active projects

2020 | 1 | 1

2020 | 2 | 2

2020 | 3 | 2

...

2020 | 9 | 0

2020 | 10 | 1

etc..

I tried the following approach ( a disconnected/unassociated calendar table):

year | month | last day of month

2020 | 1 | 2020-01-31

2020 | 2 | 2020-02-29

etc..

And then using the following formula 

(i can't copy paste it now due to auth0 issue..., so will post later)

Can you guys help me solve this?

[Dalton_Ruer · Accepted solution]

You are looking for one of my all time favorite functions which is called IntervalMatch. You can find help here:

https://help.qlik.com/en-US/sense/February2021/Subsystems/Hub/Content/Sense_Hub/Scripting/ScriptPref...

I have also attached a ZIp file which contains several different examples of how to use it for different use cases. My example is a minute by minute calculation but you will get the concept. You simply have another table for your Months and then you do an IntervalMatch of your Months to the Start/End date values. 

[dmeeuws]

Thanks a ton. I was bashing my head trying to have 'row level context' filters in Set Expressions, which didn't work as I expected.

This pointer opened up a whole new world to me. Thanks!