Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Support Cases coming to Qlik Community Oct. 4! Start chats, open cases, explore resources: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
gupta_n8
Specialist II
Specialist II

Adding Lead time Working Days

Hi All,

Suppose I am having a Date fuield as Date_From and another field of Lead Days.

Now I want to create a new column Date_End which should be Date_From +Lead Days(Working Days only).

Suppose Date from is 6-6-2014 and lead days is 10,

Then Date_End should be 20-6-2014

9 Replies
Not applicable

=date(Date_From+Lead_Days)

this expression gives Date_End

jonathandienst
Partner
Partner

Hi

=LastWorkDate(Today(1)+1, 10)

HTH

Jonathan

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
gupta_n8
Specialist II
Specialist II
Author

Hi Jonathan,

Its helpful but there issue that it will treat Mon-Fri as workdays only,

while I want a flexibility to define my own work days....

As suppose I have Mon and Tue as Holidays then what should be done

Is it possible???

gupta_n8
Specialist II
Specialist II
Author

Capture.PNG.png

Not applicable

networkdays (start:date, end_date {, holiday}) Returns the number of working days (Monday-Friday) between and including start_date and end_date taking into account any optionally listed holidays. All parameters should be valid dates or timestamps. use this

Not applicable

lastworkdate(start_date, no_of_workdays {, holiday}) Returns the earliest ending date to achieve number_of_workdays (Monday-Friday) if starting at start_date taking into account any optionally listed holidays. Start_date and holiday should be valid dates or timestamps. this is appropriate in ur case

gupta_n8
Specialist II
Specialist II
Author

Hi Sneha,

Thanks for your reply,

But the holidays have to be defined in dates, but as you can seewe have week offs different and suppose if lead days is big as 360 , 450, then how many dates we will pass......

Not applicable

see I know v have holidays different. in that expression holiday is ur customized weekoff. no_of_workdays will b ur lead time(it can be 350,450 or whatever), start_date is ur start_date. but there us a problem here . v can specify only one day as weekoff.. it has to be improvised.

tresesco
MVP
MVP

Something like:


Count:

load
*,
RangeSum(If( WildMatch([Week Offs],'*'&Weekday(StartDate+IterNo()-1)&'*'),1+Peek('Count'),Peek('Count'))) as Count
While IterNo()<=LeadDays;


Load * Inline [
StartDate, LeadDays, Week Offs
01-Jun-2014, 15, "Monday, Tuesday"
15-Jun-2014, 10, "Wednesday, Friday"
25-Jun-2014, 25, "Saturday, Tuesday"
];

Load
StartDate,
LeadDays,
Date(StartDate+LeadDays+Max(Count)) as EndDate
Resident Count Group By StartDate,LeadDays;

Drop Table Count;

PFA