topic Re: Calculating Days Difference between two dates, excluding weekends/bank holidays in QlikView

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

Tue, 16 Jun 2015 07:11:30 GMT

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!

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

maxgro — Tue, 16 Jun 2015 07:14:31 GMT

maybe some help here

Re: Calculate Elapsed Time Nett

Calculate hours between two Date/Time strings

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

martinpohl — Tue, 16 Jun 2015 07:16:58 GMT

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

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

settu_periasamy — Tue, 16 Jun 2015 07:33:22 GMT

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

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

kmarsden — Wed, 30 Sep 2015 13:47:46 GMT

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