Qlik Community

QlikView Deployment

Discussion Board for collaboration related to QlikView Deployment.

panipat1990
Contributor II

Exculde day of saturday sunday From Start Date And End Date

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

9 Replies
MVP
MVP

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

panipat1990
Contributor II

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

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

MVP
MVP

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

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.

panipat1990
Contributor II

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

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

MVP
MVP

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

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

panipat1990
Contributor II

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

Yaa Sure

MVP
MVP

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

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

Partner
Partner

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

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

Capture21.PNG

Partner
Partner

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

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)