I find your issue interesting and challenging, I am confident it could be done with Qlik, but before I start, would you please elaborate a little more on the reason to keep a Master Calendar at Client level? I am concerned about the size of the master calendar implemented at Client level.
My plan is to write a proof of concept solution!
Hope this helps (Well, I am actually asking question, before I share my ideas)
Arnaldo Sandoval A journey of a thousand miles begins with a single step.
Thanks for your questions, the master calendar is actually a change that I'm in the midst of implementing.
I actually have many different data sets from different sources which link to this Master calendar level (e.g. clients, phone calls, tasks, incidents, employees).
Before I was using "tasks" as the central fact table (as that was the only data set in the original version of the app) and joining the other data sets by client/dates, but this caused issues with evaluating correct numbers where the join didn't complete, e.g. I had a phone call on a day, but no task.
The thing the data sets generally have in common is they all have transaction dates, and they all have a client.
By creating a central fact table which is essentially a map of all client/date combinations, then connecting the data sets to this fact table, I can be completely confident that if the user selects Client A and January, all metrics from the different data sets will evaluate correctly. It also makes implementing new data sets as long as I can join them to the fact table in the same way.
Also, if I have a solution for an issue like this public holiday challenge, I expect it will be instantly re-usable across all the data sets due to using the same Master Calendar.
There are limitations to this approach - You are correct that this does create a very large data set, users do lose some functionality (e.g. show me tasks that were created in January and completed in February), and I need to apply set analysis to just about every master measure I make available in the app but overall I feel the data quality and usability benefits make this approach worthwhile.
If this creates other problems I will be finding out soon enough!