topic Re: Re: Adding Lead time Working Days in QlikView

Adding Lead time Working Days

Anonymous — Fri, 06 Jun 2014 05:59:25 GMT

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

Re: Adding Lead time Working Days

Fri, 06 Jun 2014 06:07:11 GMT

=date(Date_From+Lead_Days)

this expression gives Date_End

Re: Adding Lead time Working Days

jonathandienst — Fri, 06 Jun 2014 06:12:33 GMT

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

HTH

Jonathan

Re: Adding Lead time Working Days

Anonymous — Fri, 06 Jun 2014 06:19:24 GMT

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???

Re: Re: Adding Lead time Working Days

Anonymous — Fri, 06 Jun 2014 06:28:41 GMT

Re: Re: Adding Lead time Working Days

Fri, 06 Jun 2014 06:38:59 GMT

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

Re: Adding Lead time Working Days

Fri, 06 Jun 2014 06:41:48 GMT

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

Re: Adding Lead time Working Days

Anonymous — Fri, 06 Jun 2014 06:45:59 GMT

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......

Re: Adding Lead time Working Days

Fri, 06 Jun 2014 07:11:51 GMT

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.

Re: Re: Re: Adding Lead time Working Days

tresB — Fri, 06 Jun 2014 07:23:46 GMT

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