Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Calculating Days Difference between two dates, excluding weekends/bank holidays

Hi

I have to calculate a difference between two dates within the same table - start and end date. I should exclude weekends and bank holidays from the calculation. Normally I would do startdate - enddate as difference, but I can't in this case

I have a table that holds all bank holidays

Any ideas how this can be achieved?

Thanks!

1 Solution

Accepted Solutions
kmarsden
Partner - Contributor III
Partner - Contributor III

I know this is old but I just saw your name pop up when looking for a solution to the same problem.

I used this and it was spot on. Just change the inline table to your table holding bank holidays

Find Net Working Days

View solution in original post

4 Replies
martinpohl
Partner - Master
Partner - Master

Hello,

with networkdays (Startdate, Enddate, Holidays) you can calculate theses days.

I would define the holidays in a variable so you can change the values for future at one single point

Regards

settu_periasamy
Master III
Master III

Hi,

if you want the script, try this,

Let vYearStart = NUM(YearStart(Today()));

Let vYearEnd = NUM(YearEnd(Today()));

DATE:

LOAD

  Date($(vYearStart) + rowno() -1) as Date

AUTOGENERATE $(vYearEnd)- $(vYearStart)+1;

tmpHoliday:

Load * INLINE

[

  Holiday, Holiday_Date

  New Year, 01/01/2014

  Good Friday, 29/03/2014

  Diwali, 29/10/2014

];

tmpConcat:

LOAD concat(chr(39) & Holiday_Date & chr(39),',') as HolidayDates

RESIDENT tmpHoliday;

Let vPublicHolidays = fieldvalue('HolidayDates',1);

DROP TABLE tmpHoliday;

DROP TABLE tmpConcat;

if you want to count the working date, then you put the expression like the below

=NetWorkDays(Startdate,EndDate,$(vPublicHolidays))

kmarsden
Partner - Contributor III
Partner - Contributor III

I know this is old but I just saw your name pop up when looking for a solution to the same problem.

I used this and it was spot on. Just change the inline table to your table holding bank holidays

Find Net Working Days