# New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

New Contributor III

## Excluding days in month

Hello everyone,

How do i exclude 2nd and 4th saturday and all sundays in month??

Thanks& Regards,

Kshitija

9 Replies

## Re: Excluding days in month

HI,

Have a look at the link.

NetWorkDays

Regards,

Kaushik Solanki

MVP

## Re: Excluding days in month

May be create flags for these dates in your script.:

Table:

LOAD Date,

WeekDay(Date) as WeekDay,

If(WeekDay(Date) = 'Sun' or (WeekDay(Date) = 'Sat' and Match(Div(Day(Date), 7), 1, 3)), 1, 0) as Exclude_Flag;

LOAD Date(MakeDate(2016, 1, 1) + RecNo() - 1) as Date

AutoGenerate 366;

New Contributor III

## Re: Excluding days in month

Hi Sunny,

To be more specific i have open date and close date and calculated TAT( ie.closedate-opendate) , TAT contains all saturdays and sundays, so i want to exclude 2nd and 4th saturdays and all sundays from thoes.

Thanks&Regards,

Kshitija

Esteemed Contributor II

## Re: Excluding days in month

Add Flags to your Datefield or preferably in MasterCalendar as below

Dates = is your datefield

if(NUM(Dates) = makedate(YEar(Dates),NUM(Month(Dates)),1+7*2)-(NUM(WeekDay(MonthStart(Dates),6))+1),'2ndSat',

if(NUM(Dates) = makedate(YEar(Dates),NUM(Month(Dates)),1+7*4)-(NUM(WeekDay(MonthStart(Dates),6))+1),'4thSat')) as SaturdayFlag

Esteemed Contributor II

## Re: Excluding days in month

adding the same flags to an Intervalmatch table should do it ! your thoughts stalwar1

MVP

## Re: Excluding days in month

I feel that could work but I guess the OP is looking to do it using NetWorkDays or Subtraction of two dates which is more complicated stuff. I have seen it done before, but would require some time and effort to get it working

Esteemed Contributor II

## Re: Excluding days in month

Can you share sample data?

New Contributor III

## Re: Excluding days in month

Hi Vineeth ,

I have attached sample data , i want to calculate the TAT of opening and closing date and then exclude 2nd and 4th Saturday and all Sundays.

Thanks!

Esteemed Contributor II

## Re: Excluding days in month

Had to write a module to get this result in Qlikview,

I know you are using Qliksense but you can create a QVD using a personal edition and load that to your Qliksense app as a workaround until an someone has a better solution.

LOGIC: use networkdays to exclude all saturdays & sundays between the period and then add the number of 1st or 3rd saturdays falling in the period

MODULE:

Press CTRL + M and paste this code

Function AdjustSaturdays(StartDate, EndDate)

Days = 0

For Dates = StartDate To EndDate

''    Check for Weeknumber to identify saturday of the month

IF (Day(Dates)/7)<=1 THEN

SatNumber = 1

ELSEIF (Day(Dates)/7)>1 and (Day(Dates)/7)<=2 THEN

SatNumber = 2

ELSEIF (Day(Dates)/7)>2 and (Day(Dates)/7)<=3 THEN

SatNumber = 3

ELSEIF (Day(Dates)/7)>3 and (Day(Dates)/7)<=4 THEN

SatNumber = 4

ELSEIF (Day(Dates)/7)>4 and (Day(Dates)/7)<=5 THEN

SatNumber = 5

ELSEIF (Day(Dates)/7)>5 and (Day(Dates)/7)<=6 THEN

SatNumber = 6

END IF

If WeekDay(Dates) = 7  and (SatNumber = 1 or SatNumber = 3) THEN

Days = Days + 1

End If

Next

AdjustSaturdays = Days

End Function

IN YOUR LOAD SCRIPT:

TATDAYS:

LOAD

Dates,

Stop,

NetworkDays,

SaturdaysToAdd,

NetworkDays + SaturdaysToAdd as TATDays;

LOAD Dates,

Stop,

NetWorkDays(Dates,Stop) as NetworkDays,

AdjustSaturdays(NUM(Dates),NUM(Stop)) as SaturdaysToAdd

FROM

(ooxml, embedded labels, table is Sheet1);