holiday Query

[#1]

Hi QV Consultants,

I have a query.. It's like I NEED TO TAKE OUT NO. OF DAYS REQUIRED TO COMPLETE A TASK.. IT'S HAVING FIELDS - 'TASKOPENDATE' AND 'TASKCLOSEDDATE'.. I HAVE TO TAKE INTO ACCOUNT FOR THE WEEKENDS OR THE BANK HOLIDAYS FALLING IN BETWEEN THAT PERIOD. FOR THAT PURPOSE I' AM HAVING A HOLIDAY LIST IN THE FORM OF EXCEL.

AS OF NOW I' AM SUBTRACTING TASKCLOSEDDATE-TASKOPENDATE AND NOW ALSO NEED TO DEDUCT IF ANY HOLIDAYS FALL IN BETWEEN..

I' am stuck in this issue and if any idea/help of how to get along with this will be appreciated.

[Gysbert_Wassenaar]

You can use the networkdays function. It calculates the number of working days between two dates and you can also add holidays as additional arguments so the holidays are considered non-working days too.

networkdays(TASKOPENDATE, TASKCLOSEDDATE, holiday1, holiday2, .... etc)

Replace holiday1, holiday2 etc with valid numeric dates of the holidays.

[#3]

Hi Gysbert,

Thanks for your reply.. Actually there is a long list of all saturdays and sundays and ofcourse the bank holidays in a year.. So replacing those holidays with numeric dates won't be a feasible process to follow.

I hope you understand my concern!!

[Gysbert_Wassenaar]

No, I don't. I don't see why it's not feasible to create a list with the dates of holidays and load that into qlikview. Forget the saturdays and sundays. The networkdays function already considers those. You only need a list with the dates of the holidays. Surely your organisation has such a list somewhere, in an excel file or a database. Load that list into Qlikview.

// load the holidays in a temporary table as a comma separated list of values

TempHoliday:

Load concat(HolidayDate, ',') as HolidayList from ...somewhere...;

// create a variable with the list of holidays to use in networkdays

LET vHolidayList = peek('HolidayList');

// load the tasks and calculate the net working days per task

Tasks:

Load

TaskID,

TASKOPENDATE,

TASKCLOSEDDATE,

networkdays(TASKOPENDATE, TASKCLOSEDDATE, $(vHolidayList)) as NETDAYS

from ...tasktable...;