topic Previous Business Day in QlikView

Previous Business Day

Fri, 10 Aug 2012 12:47:50 GMT

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

Re: Previous Business Day

m_woolf — Fri, 10 Aug 2012 13:54:04 GMT

Look in help at the firstworkday() function

Re: Previous Business Day

swuehl — Fri, 10 Aug 2012 13:55:54 GMT

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;

Re: Previous Business Day

daveamz — Fri, 10 Aug 2012 14:06:40 GMT

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

Re: Previous Business Day

Fri, 10 Aug 2012 14:06:59 GMT

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

Re: Previous Business Day

swuehl — Fri, 10 Aug 2012 14:12:41 GMT

No of workdays is including the end date, so try as suggested above:

firstworkdate(Date-1, 1, $(vHolidayLIst)) as PreDay;

Re: Previous Business Day

Fri, 10 Aug 2012 14:47:49 GMT

Thanks,

Perfect!!!!