Calculate Days For Service calls

[gidon500]

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

[robert99]

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

[gidon500]

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

[robert99]

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'

[robert99]

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

[pathiqvd · Accepted solution]

Hi,

    Try This

A:

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

     workday

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

[gidon500]

hI

Thank you very much

it works

gidon

[robert99]

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

[robert99]

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 (biff, embedded labels, table is Sheet1$) ;

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

(ooxml, embedded labels, table is Sheet1);

dimension:-

DOCNO

expression:-

sum(workday)

[gidon500]

Hi

thanks you very much

I got an answer

happy new year

gidon