7 Replies Latest reply: May 7, 2013 5:03 AM by Prashant Adhav

# REMOVE 2nd and 3rd Saturdays and Sundays.

Hi to All,

Need help into this,

Got 2 dates viz. REC_DATE, DEP_DATE

TAT= DEP_DATE - REC_DATE

Suppose the REC_DATE is 1 Dec'11 and DEP_DATE is 13 Dec'11

while calculating i have to remove 2nd and 3rd saturday and also all sundays.

Please  share some hints on the same.

Have used NETWORKDAYS bt tht remove all weekends..

Thanks to All for considering my Querry....

Prashant A.

• ###### REMOVE 2nd and 3rd Saturdays and Sundays.

Hi,

On this community link you would be able to find your problem solution.

Regards,

Nitin Jain

• ###### Re: REMOVE 2nd and 3rd Saturdays and Sundays.

Below is a simple expression to flag the dates you require in order to remove them:

=if(day(Date)/7>1 and day(Date)/7<=3 and (WeekDay(Date)='Sat' or WeekDay(Date)='Sun'),'Remove','Keep')

Hope that helps,

Matt - Visual Analytics Ltd

• ###### Re: REMOVE 2nd and 3rd Saturdays and Sundays.

Hi

Please try the macro function if you find suitable.

function NoOfSat(Sdate,Edate)

StrDate=Sdate'"10/01/2011"

EndDate=Edate'"10/29/2011"

'StrDate="01/01/2011"

'EndDate="01/29/2011"

CountI=datediff("d",StrDate,EndDate)

'if CountI=0 then CountI=1

'Test1= DateSerial(year(StrDate) ,month(StrDate),datepart("d",StrDate))

SatCount=0

DtPartFlag=datepart("d",StrDate) '' to enter in for loop

w=""

J=1

i=0

'for i= 0 to CountI step +1

while i<=CountI

DtPart=datepart("d",(StrDate)+i)

WkDay=weekday((StrDate)+i)

vDateNum= year(StrDate+i)*10000+ month(StrDate+i)*100+datepart("d",StrDate+i)

if WkDay=7 then J=7

if not(DtPart>=8 and DtPart<=21) and WkDay=7 and not (vDateNum=20110101 or vDateNum=20110205 or vDateNum=20110226) then ''and SatCount<2

SatCount=SatCount+1

'J=7

end if

i=i+J

'w=w & ":" & DtPart

wend

NoOfSat=SatCount

end function

let T1=now();

load NoOfSat('12/03/2011','12/31/2011') as Test autogenerate 10000;

let T2=now();

• ###### Re: REMOVE 2nd and 3rd Saturdays and Sundays.

Hi ,

You can play around the solution mentioned in this thread.

http://community.qlik.com/message/335201

Regards

Yusuf

• ###### Re: REMOVE 2nd and 3rd Saturdays and Sundays.

Hey Yusuf,

The example stated by you is good for detecting days but w.r.t to my requirement hw can i eliminate those days from the subtraction operation while calculating TAT.

Regards,

• ###### Re: REMOVE 2nd and 3rd Saturdays and Sundays.

Hi:

There is no function in the world that can tell you what labor days exist on each community. For example in Spain, holidays go by communities. Madrid has their own, Cataluña their own... In fact, thay are companies having different holidays in the same city.

What you need is a calendar of the company you are refering to, and that's all. Once you have that information, arrange your calculations acording to that calendar.

• ###### Re: REMOVE 2nd and 3rd Saturdays and Sundays.

Hey Chris,

i have the holiday list with me handy for the next 2 yrs, but how will i use that for getting my calculations done.

TAT= DEP_DATE - REC_DATE

.

.

.