Qlik Community

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Read about the latest Qlik Community enhancements on the Community News blog!
cancel
Showing results for 
Search instead for 
Did you mean: 
thomasmercer
Contributor III
Contributor III

Calculating number of business days with multiple regions for public/bank holidays

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

TransactionDateClientTransaction reference
02 Nov 2020ClientA123
02 Nov 2020ClientB456

 

MasterCalendar - has records for every date/client combination. Joins to transactions on client and date

MasterDateClientRegion
02 Nov 2020ClientAVIC
02 Nov 2020ClientBNSW

 

WorkDaysCalendar - has records for every date/region combination. Joins  to mastercalendar on date and region

WorkDaysDateRegionWorkDay
02 Nov 2020VIC1
02 Nov 2020NSW1
03 Nov 2020VIC0
03 Nov 2020NSW1
04 Nov 2020VIC1
04 Nov 2020NSW1
05 Nov 2020VIC1
05 Nov 2020NSW1
06 Nov 2020VIC1
06 Nov 2020NSW1

 

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?

2 Replies
ArnadoSandoval
Specialist II
Specialist II

Hi @thomasmercer 

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.
thomasmercer
Contributor III
Contributor III
Author

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!