9 Replies Latest reply: Dec 30, 2016 4:06 AM by gidon david RSS

    Calculate Days For Service calls

    gidon david

      Hi Guys

      i need help with this issue

      I have a request to calculate working  days for each service call

       

      From open date of  service call  DOCDATE

      To closed  date of service call CLOSEDATE

       

      i need to calculate for each call how many working days  are from open to close  .

      enclosed 2 files  ,

      first if list of service calls with dates

      second  list of dates , and a filed Workday  only the ones marked with 1 are working days

       

      thanks 

      and happy new year

      gidon

        • Re: Calculate Days For Service calls
          Robert Hutchings

          Hi Gidon

           

          The issue you seem to have is these date seem to be in a text format not a numeric date format

           

          So you need to use DATE#

           

          TRY

           

          date#(DOCDATE, 'DD/MM/YY')

           

          Then once these date's are converted into numeric dates subtract one from the other

           

          or use Navigation >>>  Data Manager and this will automatically create the appropriate script as follows

           

          [Sheet1$]:

          LOAD

              [DOCNO],

              Date(Date#([DOCDATE], 'DD/MM/YYYY') ,'DD/MM/YYYY') AS [DOCDATE],

              Date(Date#([CLOSEDATE], 'DD/MM/YYYY') ,'DD/MM/YYYY') AS [CLOSEDATE]

          FROM [lib://Downloads/Copy of ServiceCalls.xls]

          (biff, embedded labels, table is Sheet1$);

           

           

           

          or use Dates.JPG

            • Re: Calculate Days For Service calls
              gidon david

              Hi  Robert

               

              thanks for your fast response 

              that is correct , but i need to count only the dates that are working  days

              in the second file

               

              any idea ?

              gidon

                • Re: Calculate Days For Service calls
                  Robert Hutchings

                  I haven't done it as you want to do it

                   

                  But I did once excluding public holidays and Sat and sun

                   

                  I used networkdays and the public holidays loaded as a set statement.

                   

                  Im sure there will be a Function that only excludes days loaded from Excel

                   

                  SET vHolDates =

                  '02/01/2012','06/04/2012','09/04/2012','07/05/2012','04/06/2012','05/06/2012','27/08/2012','25/12/2012','26/12/2012'

                  ,'01/01/2013','29/03/2013','01/04/2013','06/05/2013','27/05/2013','26/08/2013','25/12/2013','26/12/2013'

                  ,'01/01/2014','18/04/2014', '21/04/2014', '05/05/2014','26/05/2014','25/08/2014','25/12/2014','26/12/2014'

                  ,'01/01/2015','03/04/2015', '06/04/2015', '04/05/2015','25/05/2015','31/08/2015','25/12/2015','28/12/2015'

                  ,'01/01/2016','25/03/2016', '28/03/2016', '02/05/2016','30/05/2016','29/08/2016', '26/12/2016','27/12/2016'

                  ,'02/01/2017','14/04/2017', '17/04/2017', '01/05/2017','29/05/2017','28/08/2017', '25/12/2017','26/12/2017'

                  • Re: Calculate Days For Service calls
                    Robert Hutchings

                    Hi

                     

                    I HAven't much time BUT this might work somehow

                     

                    What about loading the second file and converting all to dates

                     

                    Then do an if statement in script

                     

                    if (CalendarDate = Workingday, 'YES','NO') as workingDay

                     

                    the SOMETHING LIKE

                    sum ({<workingDay = {YES}>} CLOSEDATE - DOCDATE)

                     

                    NB Only CLOSEDATE is in text format

                     

                    edit you would even need to do the if statement. Just join the working day file to the DOCDATE  input

                     

                    Ill leave you to it to see if it works or not

                • Re: Calculate Days For Service calls
                  lakshmipathi p

                  Hi,

                   

                      Try This

                   

                  A:

                  LOAD date(DOCDATE,'DD/MM/YYYY') as DOCDATE1,

                       workday

                  FROM [C:\Users\lakshmipathi.p\Downloads\WorkDay.xls] (biff, embedded labels, table is Sheet1$);

                   

                   

                  B:

                  LOAD DOCNO,

                  date(DOCDATE,'DD/MM/YYYY') as DOCDATE,

                  date(date#(CLOSEDATE,'DD/MM/YYYY'),'DD/MM/YYYY') as CLOSEDATE

                  FROM [C:\Users\lakshmipathi.p\Downloads\ServiceCalls.xls] (biff, embedded labels, table is Sheet1$);

                   

                   

                  left join(B)

                  IntervalMatch(DOCDATE1)

                  C:

                  LOAD DOCDATE,

                       CLOSEDATE

                       Resident B;

                   

                  left join(B)

                  LOAD *

                  Resident A;

                   

                  drop table A;


                  exit SCRIPT;


                  after take

                  dim:-

                  1)DOCNO

                  2)DOCDATE

                  3)CLOSECDATE

                  exp:-

                  sum(workday)



                  • Re: Calculate Days For Service calls
                    Robert Hutchings

                    After checking this to see if another way could eb used rather than interval match (or a non script option)

                     

                    Networkdays only applies where 2 days are consistently without exception non work days every week. There is no QLIK formula that excludes Saturday and Sunday and instead only loads holidays. So this example has to be done in script

                     

                    The default is Saturday and Sunday but these can be say Sun and Monday (by adding or subtracting from the dates

                    >>>>  Networkdays (DOCDATE + 1, CLOSEDATE +1) >>>> Sunday and Monday

                     

                    also to load other holiday dates from say and excel spreadsheet (rather than how I have done above)

                     

                    Temp_Hols: // too load all

                    LOAD

                        DOCDATE,

                        workday,

                        IF(workday = '1',DOCDATE,null()) as Holiday

                    FROM [lib://Downloads/Copy of WorkDay.xlsx]

                    (ooxml, embedded labels, table is Sheet1);

                     

                    let vHols = '';

                    for iHol = 0 to NoOfRows('Temp_Hols') -1

                    let vHols = vHols & if(iHol > 0, ',', '') & chr(39) & Date(peek('Holiday', iHol, 'Temp_Hols')) & chr(39);

                    next

                    • Re: Calculate Days For Service calls
                      Robert Hutchings

                      I've been doing a bit of work to update my knowledge on this subject

                       

                      A way to do this without using IntervalMatch (that I try to avoid if possible). Both give the same values for sum(workday) except for a different Total

                       

                      thanks to an excellent post by Henric

                       

                      Creating Reference Dates for Intervals

                       

                      B:

                      LOAD DOCNO,

                      date(DOCDATE,'DD/MM/YYYY') as DOCDATE,

                      date(date#(CLOSEDATE,'DD/MM/YYYY'),'DD/MM/YYYY') as CLOSEDATE

                      FROM [lib://Downloads/Copy of ServiceCalls.xls]

                      (biff, embedded labels, table is Sheet1$);

                       

                      LinkTable:

                      Load

                      DOCNO,

                      Date( DOCDATE + IterNo() - 1 ) as DOCDATE1

                      Resident B

                      While IterNo() <= CLOSEDATE - DOCDATE + 1 ;

                       

                      A:

                      LOAD

                      date(DOCDATE,'DD/MM/YYYY') as DOCDATE1,

                      workday

                      //FROM [C:\Users\lakshmipathi.p\Downloads\WorkDay.xls] (biff, embedded labels, table is Sheet1$) ;

                      FROM [lib://Downloads/Copy of WorkDay.xlsx]

                      (ooxml, embedded labels, table is Sheet1);

                       

                      LinkTable.JPG

                      dimension:-

                      DOCNO

                      expression:-

                      sum(workday)