topic Remove 2nd, 3rd saurdays frm datediff in QlikView

Remove 2nd, 3rd saurdays frm datediff

Tue, 28 Feb 2012 09:50:42 GMT

Hi All,

I have to find diff between 2 dates but 2nd and 3rd saturdays, all sundays should not be considered in the minus operation

Have used macro to find the datediff between the dates also have detected the removable days from the two dates as if the flag, Remove and Keep for the days

the problem is , How should i eliminate those days from the datediff.

Please help me into this, this is the second time i am posting this Qtn.

Mcro used :

function datediff2(d1,d2)

datediff2=datediff("D",d1,d2)

end function

Expression used to detect the days :

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

Remove 2nd, 3rd saurdays frm datediff

Jason_Michaelides — Tue, 28 Feb 2012 10:00:52 GMT

Didn't Matt's solution to your previous post help then?

http://community.qlik.com/message/171177#171177

Remove 2nd, 3rd saurdays frm datediff

Tue, 28 Feb 2012 10:10:55 GMT

its helping to detect the days perfectly but i want to eliminate those while taking the difference in the dates, that will be considered as TAT which is what i exactly have to show

Please consider this

Remove 2nd, 3rd saurdays frm datediff

CELAMBARASAN — Tue, 28 Feb 2012 10:16:20 GMT

Hi,

Try with this big expression.

=Floor(interval(WeekStart(vEnd) - WeekEnd(vStart),'D') - interval(WeekStart(vEnd) - WeekEnd(vStart),'D')/7)+(6-WeekDay(vStart)*1)+NumMin(6,WeekDay(vEnd)+1)-NumMin(2,interval(WeekStart(vEnd) - WeekEnd(vStart),'D')/7)

Celambarasan

Remove 2nd, 3rd saurdays frm datediff

Tue, 28 Feb 2012 11:10:19 GMT

Not Working shows as function(datediff2 used for date difference) is not valid.

Remove 2nd, 3rd saurdays frm datediff

CELAMBARASAN — Tue, 28 Feb 2012 11:28:37 GMT

Hi,

Press Ctrl+alt+v

add a variable vDatediff2

set value as below

='Floor(interval(WeekStart($1) - WeekEnd($2),chr(39)& D &chr(39)) - interval(WeekStart($1) - WeekEnd($2),chr(39)&D&chr(39))/7)+(6-WeekDay($2)*1)+NumMin(6,WeekDay($1)+1)-NumMin(2,interval(WeekStart($1) - WeekEnd($2),chr(39)&D&chr(39))/7)'

Then use $(vDatediff2(EndDate,StartDate))

Hope it helps

Celambarasan

Remove 2nd, 3rd saurdays frm datediff

Tue, 28 Feb 2012 12:28:10 GMT

Celam,

Its not working,

I got 2 dates viz. Dep_date 2 feb 2012

Rec_date 20 Feb 2012

If i do the datediff for this answer will be 18

but there are date 11 and date 18 which are saturdays and three sundays

therefore the answer would be 13

Pls help

Remove 2nd, 3rd saurdays frm datediff

perumal_41 — Tue, 28 Feb 2012 12:49:28 GMT

Please upload sample Application and Data

Regards

Perumal A

Remove 2nd, 3rd saurdays frm datediff

CELAMBARASAN — Tue, 28 Feb 2012 12:52:51 GMT

Hi,

What it returns as answer?

I think it return answer as 14?

Celambarasan

Remove 2nd, 3rd saurdays frm datediff

CELAMBARASAN — Wed, 29 Feb 2012 05:17:03 GMT

Hi,

Check with this

='(Num(NetWorkDays($1,$2))+Floor(( WeekEnd($2) - WeekStart($1))/7))-NumMin(2,Floor(WeekStart($1) - WeekEnd($2))'

Celambarasan