topic Count the no. of Saturdays between two dates in QlikView

Count the no. of Saturdays between two dates

Thu, 20 Oct 2011 11:48:46 GMT

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

Count the no. of Saturdays between two dates

Sokkorn — Thu, 20 Oct 2011 12:40:32 GMT

Hi Rajiv,

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

Regards,

Sokkorn

Re: Count the no. of Saturdays between two dates

Thu, 20 Oct 2011 12:43:48 GMT

Hi Sokkorn,

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

Regards,

Rajiv

Count the no. of Saturdays between two dates

richard_chilvers — Thu, 20 Oct 2011 14:31:09 GMT

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

Count the no. of Saturdays between two dates

jonathandienst — Thu, 20 Oct 2011 14:45:57 GMT

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

Re: Count the no. of Saturdays between two dates

Sokkorn — Fri, 21 Oct 2011 00:51:27 GMT

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

Re: Count the no. of Saturdays between two dates

Tue, 18 Nov 2014 06:01:02 GMT

Follow this link

Re: Count the number of saturdays & sundays between two dates