Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Not applicable

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.

7 Replies
Not applicable

REMOVE 2nd and 3rd Saturdays and Sundays.

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
Valued Contributor

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

Not applicable

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

Not applicable

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

Not applicable

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,

christian77
Valued Contributor

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.

Not applicable

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

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

.

.

.

Community Browser