topic Re: REMOVE 2nd and 3rd Saturdays and Sundays. in QlikView

REMOVE 2nd and 3rd Saturdays and Sundays.

Thu, 08 Dec 2011 07:47:54 GMT

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.

Thu, 08 Dec 2011 08:19:07 GMT

Hi,

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

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

Regards,

Nitin Jain

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

matt_crowther — Thu, 08 Dec 2011 10:19:45 GMT

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.

Mon, 15 Apr 2013 10:17:47 GMT

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.

Wed, 17 Apr 2013 12:06:58 GMT

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.

Mon, 06 May 2013 06:51:04 GMT

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.

christian77 — Mon, 06 May 2013 10:13:11 GMT

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.

Tue, 07 May 2013 09:03:11 GMT

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