Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Hi,
On this community link you would be able to find your problem solution.
http://community.qlik.com/thread/39168
Regards,
Nitin Jain
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
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();
Hi ,
You can play around the solution mentioned in this thread.
http://community.qlik.com/message/335201
Regards
Yusuf
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,
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.
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
.
.
.