Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Everyone.
I would like to find previous business day(Working day), Which should exclude holiday and week ends get next previous available date.
If i am processing Monday, I should get Friday as working day, if Friday is holiday then I should get Thursday as working day. I hope I am clear.
I tried following code but could not get the result. I am able to get it Oracle by connect by clause in function. I am not sure how to do it in Qlikview load script.
HolidayTAb:
load
chr (39) & concat(Holidate,chr(39)&' ,'&chr(39) ) & chr(39) as Holidaylist
Inline [
Holidate, HolidayName
07/04/2012, Indepence day
09/03/2012, labor day
12/25/2012, X'mas
];
let vHolidayLIst=peek('Holidaylist');
InputDate:
LOAD Date,
networkdays (Date, Date ,$(vHolidayLIst)) as netdays,
Date-networkdays (Date, Date ,$(vHolidayLIst)) as PreDay
;
LOAD
Date(Date#(20120701,'YYYYMMDD')+RecNo(),'MM/DD/YYYY') AS Date
AUTOGENERATE Today()+30 - MakeDate(2012, 7, 1) + 1;
Thank you in advance
No of workdays is including the end date, so try as suggested above:
firstworkdate(Date-1, 1, $(vHolidayLIst)) as PreDay;
Look in help at the firstworkday() function
Please check this date functions in QV:
firstworkdate(end_date, no_of_workdays {, holiday} )
LOAD
Date,
networkdays(Date,Date,$(vHolidayLIst)) as netdays,
firstworkdate(Date, 2, $(vHolidayLIst)) as PreDay;
LOAD
...
edit: if Date could be a weekend / holiday, it is probably better to use
firstworkdate(Date-1, 1, $(vHolidayLIst)) as PreDay;
It works with :
InputDate:
LOAD Date,
networkdays (Date, Date,$(vHolidayLIst)) as netdays,
FirstWorkDate (Date,1,$(vHolidayLIst)) as PreDay
;
Date | netdays | PreDay |
07/02/2012 | 1 | 7/2/2012 |
07/03/2012 | 1 | 7/3/2012 |
07/04/2012 | 0 | 7/3/2012 |
07/05/2012 | 1 | 7/5/2012 |
07/06/2012 | 1 | 7/6/2012 |
07/07/2012 | 0 | 7/6/2012 |
07/08/2012 | 0 | 7/6/2012 |
07/09/2012 | 1 | 7/9/2012 |
07/10/2012 | 1 | 7/10/2012 |
Regards,
David
Thanks for reply,
Please data i am getting by using this function, stil not quite there. go 07/06/2012, i should get 07/06/2012 and for 07/05/2012 i should get 07/03/2012
InputDate:
LOAD Date,
networkdays (Date-2, Date ,$(vHolidayLIst)) as netdays,
firstworkdate(Date,1,$(vHolidayLIst)) as PreDate
;
LOAD
Date(Date#(20120701,'YYYYMMDD')+RecNo(),'MM/DD/YYYY') AS Date
AUTOGENERATE Today()+30 - MakeDate(2012, 7, 1) + 1;
Thanks
No of workdays is including the end date, so try as suggested above:
firstworkdate(Date-1, 1, $(vHolidayLIst)) as PreDay;
Thanks,
Perfect!!!!