Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Count the no. of Saturdays between two dates

Hi,

Could someone suggest how to calculate the no. of Saturdays between "Start Date" and "End Date". I also have a list of holidays. If the saturday is a holiday, then it should not get counted.

I am trying to get this at load time. So I have a table as follows:

Rec No.             Start Date               End Date          Holidays.

Now I want to add another field through a script called NoOfSaturdays by calculating the no of Saturdays between "Start Date" and "End Date" excluding the holidays.

Any help would be appreciated.

Regards,

Rajiv

6 Replies
Sokkorn
Master
Master

Hi Rajiv,

Try to use function: networkdays(start_date, end_date {, holiday} )

Regards,

Sokkorn

Not applicable
Author

Hi Sokkorn,

Networkdays() excludes Saturdays and Sundays. Any similar function which counts only Saturdays?

Regards,

Rajiv

richard_chilvers
Specialist
Specialist

Hi Rajiv

There doesn't seem to be a similar function for Saturdays.

However, it may be useful to work on the basis that Saturday always follows Friday. So perhaps count the number of Fridays and then work from there. It might not be so easy to exclude holidays though.

Good luck.

Richard

jonathandienst
Partner - Champion III
Partner - Champion III

Rajiv

If you include a holidays flag and a day of week field in a master calendar during, then this will be very easy to calculate. If you are not sure about a master calendar, search on this forum for master calendas there already a lot of articles.

To create a holiday flag, you will need to test the dates (when you build the calendar) against the list of holidays. You could do this be reading the holidays into a table with the date and holiday name, and left joining this to the master calendar.

Hope this helps

Jonathan

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Sokkorn
Master
Master

Good Morning Rajiv,

Use this script

Temp:

LOAD * Inline [

RecNo,StartDate,EndDate

1,01/01/2011,01/03/2011

2,03/05/2011,03/05/2011

];

SET vRecCount = NoOfRows('Temp')-1;

FOR i = 0 TO $(vRecCount)

    vMinDate = NUM(PEEK('StartDate',$(i),'Temp'));

    vMaxDate = NUM(PEEK('EndDate',$(i),'Temp'));

    vCode = PEEK('RecNo',$(i),'Temp');

    IF i = 0 THEN

        recCount = 0;

    ELSE

        recCount = NoOfRows('TempCalendar');

    ENDIF

    TempCalendar:

    LOAD $(vCode) AS RecNo2,     

      Date($(vMinDate) + RowNo()-$(recCount)-1) AS TempDate,

     WEEKDAY(Date($(vMinDate) + RowNo()-$(recCount)-1))    AS MyWeekDay,

     IF(WEEKDAY(Date($(vMinDate) + RowNo()-$(recCount)-1))='Sat',1,0)    AS MyHoliday

    AUTOGENERATE 1

    WHILE $(vMinDate)+IterNo()-1<= $(vMaxDate);

NEXT

Regards,

Sokkorn

Not applicable
Author