3 Replies Latest reply: Mar 1, 2013 1:20 PM by Gysbert Wassenaar RSS

    holiday Query

    Gaurav Khare

      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

          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
              Gaurav Khare

              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

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