topic Re: Calculate Days For Service calls in QlikView

Calculate Days For Service calls

gidon500 — Thu, 29 Dec 2016 09:14:45 GMT

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

robert99 — Thu, 29 Dec 2016 09:28:38 GMT

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

Re: Calculate Days For Service calls

gidon500 — Thu, 29 Dec 2016 09:43:39 GMT

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

robert99 — Thu, 29 Dec 2016 10:15:25 GMT

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

robert99 — Thu, 29 Dec 2016 10:30:28 GMT

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

pathiqvd — Thu, 29 Dec 2016 10:48:45 GMT

Hi,

Try This

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

workday

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

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)

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

gidon500 — Thu, 29 Dec 2016 11:25:04 GMT

Thank you very much

it works

gidon

Re: Calculate Days For Service calls

robert99 — Fri, 30 Dec 2016 07:33:07 GMT

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

Re: Calculate Days For Service calls

robert99 — Fri, 30 Dec 2016 08:32:53 GMT

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

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 ;

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)

Re: Calculate Days For Service calls

gidon500 — Fri, 30 Dec 2016 09:06:53 GMT

thanks you very much

I got an answer

happy new year

gidon