Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Hi,
Try This
A:
LOAD date(DOCDATE,'DD/MM/YYYY') as DOCDATE1,
workday
FROM
B:
LOAD DOCNO,
date(DOCDATE,'DD/MM/YYYY') as DOCDATE,
date(date#(CLOSEDATE,'DD/MM/YYYY'),'DD/MM/YYYY') as CLOSEDATE
FROM
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)
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
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
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'
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
Hi,
Try This
A:
LOAD date(DOCDATE,'DD/MM/YYYY') as DOCDATE1,
workday
FROM
B:
LOAD DOCNO,
date(DOCDATE,'DD/MM/YYYY') as DOCDATE,
date(date#(CLOSEDATE,'DD/MM/YYYY'),'DD/MM/YYYY') as CLOSEDATE
FROM
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)
hI
Thank you very much
it works
gidon
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
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
FROM [lib://Downloads/Copy of WorkDay.xlsx]
(ooxml, embedded labels, table is Sheet1);
dimension:-
DOCNO
expression:-
sum(workday)
Hi
thanks you very much
I got an answer
happy new year
gidon