Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi, I have a bit of a challenge where I have a date in my data and need to calculate the day which is two business days ahead of the date as a "date due".
To make this more complicated I have different "regions" in the data, which have different public/bank holidays.
I've setup the data model so that it has all this data linked to a master calendar, data (simplified) looks something like below:
Transactions
TransactionDate | Client | Transaction reference |
02 Nov 2020 | ClientA | 123 |
02 Nov 2020 | ClientB | 456 |
MasterCalendar - has records for every date/client combination. Joins to transactions on client and date
MasterDate | Client | Region |
02 Nov 2020 | ClientA | VIC |
02 Nov 2020 | ClientB | NSW |
WorkDaysCalendar - has records for every date/region combination. Joins to mastercalendar on date and region
WorkDaysDate | Region | WorkDay |
02 Nov 2020 | VIC | 1 |
02 Nov 2020 | NSW | 1 |
03 Nov 2020 | VIC | 0 |
03 Nov 2020 | NSW | 1 |
04 Nov 2020 | VIC | 1 |
04 Nov 2020 | NSW | 1 |
05 Nov 2020 | VIC | 1 |
05 Nov 2020 | NSW | 1 |
06 Nov 2020 | VIC | 1 |
06 Nov 2020 | NSW | 1 |
Expected result is the VIC client transaction record should calculate 05 Nov 2020, while for NSW it should be 04 Nov 2020.
I also use ETL tool EasyMorph - is this perhaps better to calculate and add to the data set pre-load?
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)
Hi @ArnadoSandoval,
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!