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)
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.
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.
