Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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

1 Solution

Accepted Solutions
swuehl
MVP
MVP

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

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

View solution in original post

6 Replies
m_woolf
Master II
Master II

Look in help at the firstworkday() function

swuehl
MVP
MVP

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;

daveamz
Partner - Creator III
Partner - Creator III

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
Author

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

swuehl
MVP
MVP

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

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

Not applicable
Author

Thanks,

Perfect!!!!