REMOVE 2nd and 3rd Saturdays and Sundays.

[#1]

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.

[#2]

Hi,

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

http://community.qlik.com/thread/39168

Regards,

Nitin Jain

[matt_crowther]

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

[#4]

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();

[#5]

Hi ,

      You can play around the solution mentioned in this thread.

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

Regards

Yusuf

[#6]

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,

[christian77]

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.

[#8]

Hey Chris,

Thks for the reply

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

.

.

.