topic Re: holiday Query in QlikView

holiday Query

Fri, 01 Mar 2013 16:32:18 GMT

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.

Re: holiday Query

Gysbert_Wassenaar — Fri, 01 Mar 2013 17:51:06 GMT

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.

Re: holiday Query

Fri, 01 Mar 2013 17:55:26 GMT

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

Re: holiday Query

Gysbert_Wassenaar — Fri, 01 Mar 2013 18:19:26 GMT

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...;