topic Re: Exculde day of saturday sunday From Start Date And End Date in QlikView

Exculde day of saturday sunday From Start Date And End Date

panipat1990 — Mon, 26 Jan 2026 18:19:17 GMT

Dear Team,

i have two Dates Start Date End Date

01-04-2016 21-08-2016

03-04-2016 23-09-2016

03-05-2016 23-10-2016

And Third Field ID :-101,102,103

I want to calcualte Day Count From Start Date to End Date Exculude Saturday And Sunday..

Kindly Help Me

Thanks

Gourav

Re: Exculde day of saturday sunday From Start Date And End Date

tresB — Tue, 08 May 2018 05:47:51 GMT

Use networkdays()

Re: Exculde day of saturday sunday From Start Date And End Date

panipat1990 — Tue, 08 May 2018 05:53:21 GMT

Kindly Send me Code And If Possible Can you send me qvw

Re: Exculde day of saturday sunday From Start Date And End Date

tresB — Tue, 08 May 2018 06:02:06 GMT

Gourav, it's pretty easy, you have to just pass two values/fields in the function. Try it yourself; I guess for passing two arguments in a function shouldn't require help. If you don't understand a specific area of it, you could ask.

Re: Exculde day of saturday sunday From Start Date And End Date

panipat1990 — Tue, 08 May 2018 06:11:30 GMT

For this i am attaching data .. i used network Funcation But Proper Result is not coming ...

Re: Exculde day of saturday sunday From Start Date And End Date

tresB — Tue, 08 May 2018 06:13:57 GMT

Could you share the qvw instead where you tried and found result was not coming as expected?

Re: Exculde day of saturday sunday From Start Date And End Date

panipat1990 — Tue, 08 May 2018 06:18:02 GMT

Yaa Sure

Re: Exculde day of saturday sunday From Start Date And End Date

tresB — Tue, 08 May 2018 06:20:59 GMT

Thanks for sharing. Now help me with an example (tell me a row) which is not right you found.

Re: Exculde day of saturday sunday From Start Date And End Date

vishalarote — Tue, 08 May 2018 07:32:43 GMT

Maybe it will help you

t1:

load *,

WeekDay([Start Date]) as StartDay,

weekday([End Date]) as EndDay

where weekday([Start Date])<>WeekDay('7') and WeekDay([Start Date])<>WeekDay('1') and weekday([End Date])<>WeekDay('7') and WeekDay([End Date])<>WeekDay('1');

LOAD ID,

[Start Date],

[End Date]

FROM

(ooxml, embedded labels, table is Sheet2);

Re: Exculde day of saturday sunday From Start Date And End Date

vishalarote — Tue, 08 May 2018 11:12:39 GMT

Check this updated sample file

first create one calendar whos having 2016-2018 dates

then apply logic for removing sat and sun like

where weekday(Date)<>WeekDay('7') and WeekDay(Date)<>WeekDay('1')

make it all dates rename as Date and left join your data with calendar dates.

t2:

load

Date(MakeDate(2016)+recNo()-1,'DD/MM/YYYY') as Date

AutoGenerate 365;

Concatenate

load

Date(MakeDate(2017)+recNo()-1,'DD/MM/YYYY') as Date

AutoGenerate 365;

Concatenate

load

Date(MakeDate(2018)+recNo()-1,'DD/MM/YYYY') as Date

AutoGenerate 365;

store t2 into 'C:\Users\Mehul\Downloads\DateField.qvd'(qvd);

drop table t2;

t1:

load *,

WeekDay(Date) as StartDay

where weekday(Date)<>WeekDay('7') and WeekDay(Date)<>WeekDay('1');

LOAD ID,

[Start Date] as Date,

'start' as Flag

FROM

(ooxml, embedded labels, table is Sheet2);

Concatenate(t1)

load *,

weekday(Date) as EndDay

where weekday(Date)<>WeekDay('7') and WeekDay(Date)<>WeekDay('1');

LOAD ID,

[End Date] as Date,

'End' as Flag

FROM

(ooxml, embedded labels, table is Sheet2);

store t1 into 'C:\Users\Mehul\Downloads\DateField2.qvd'(qvd);

drop table t1;

t3:

LOAD Date,

WeekDay(Date) as StartDay1,

WeekDay(Date) as EndDay1

FROM

(qvd)

where weekday(Date)<>WeekDay('7') and WeekDay(Date)<>WeekDay('1');

left join(t3)

LOAD ID,

Date,

Flag,

StartDay,

EndDay

FROM

(qvd);

make the variables

vMinStartDate=Date(min({<Flag={'Start'}>}Date))

vMaxEndDate=Date(max({<Flag={'End'}>}Date))

use the set analysis

=Count(DISTINCT {<Date={">=$(vMinStartDate)<=$(vMaxEndDate)"}>}Date)