Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Not applicable

Previous Business Day

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

Tags (3)
1 Solution

Accepted Solutions
MVP
MVP

Re: Previous Business Day

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

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

6 Replies
mwoolf
Honored Contributor II

Re: Previous Business Day

Look in help at the firstworkday() function

MVP
MVP

Re: Previous Business Day

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;

Highlighted
daveamz01
Contributor III

Re: Previous Business Day

It works with :

InputDate:

LOAD Date,

networkdays (Date, Date,$(vHolidayLIst)) as netdays,

FirstWorkDate (Date,1,$(vHolidayLIst)) as PreDay

;

DatenetdaysPreDay
07/02/201217/2/2012
07/03/201217/3/2012
07/04/201207/3/2012
07/05/201217/5/2012
07/06/201217/6/2012
07/07/201207/6/2012
07/08/201207/6/2012
07/09/201217/9/2012
07/10/201217/10/2012

Regards,

David

Not applicable

Re: Previous Business Day

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

MVP
MVP

Re: Previous Business Day

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

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

Not applicable

Re: Previous Business Day

Thanks,

Perfect!!!!