Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld online is next week! REGISTER 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;

daveamz01
Creator III
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!!!!